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

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

reply posted in original thread.

--

Ken Snell
MS ACCESS MVP

"Bob M." wrote in message
...
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