Hey Van, Ken Douglas and others... Need Query help again please...

  • Thread starter Thread starter Bob M.
  • Start date Start date
B

Bob M.

Hey guys... Thanks so much. Van... I put in your code and it worked.

I HAVE ONE PROBLEM THOUGH.

Below is actual output from query (except frist 2 lines):... problem is
stated after sample data.....

Rec# FileID SS# Lastname Firstname
======|======|========|============|==========
Rec# 20 Fl_u015 229806158 RORER JOHN
Rec# 21 Fl_u015 229806158 RORER JOHN
Rec# 22 Fl_u015 239605327 CRUZ ISMAEL
Rec# 23 Fl_u015 239605327 CRUZ ISMAEL
Rec# 24 Fl_u015 242415467 VANDERHAAR KIRK
Rec# 25 Fl_u015 242415467 VANDERHAAR KIRK
Rec# 26 Fl_u015 254905806 SWAIN BERT
Rec# 27 Fl_u015 254905806 SWAIN BERT
Rec# 28 Fl_u015 261252957 FULLERTON MICHAEL
Rec# 29 Fl_u015 261588101 PEREZ MARGARET
Rec# 30 Fl_u015 261840282 DASH LORRAINE


You will notice that there are duplicate soc sec. numbers until rec# 28.
Upon inspection of the database I was stumped. There is a previos run with
FILEID named fl_u014. The query picked up duplicate ss#'s from the fl_U014
and erroneously populated the recordset with it. I only want duplicate
ss#'s for one particular FILEID (fl_u015) which is hard coded into the SQL
statement at this tiome but will be a varible in the future. I don't know
SQL good enough to figure out what conditional is missing and from where in
the statement.

Here's the code again:

cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (FileId= ""Fl_u015"" )" & _
"ORDER BY EmployeeSSNumber"


Thanks again everyone for all your help. ..... BOB
 
Back
Top