Query works and doesn't work

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

Bob,

Not sure where the problem is, although I think I would preface all of the
fields in the IN clause with the aliased tablename like below. I also
perfer to insert quotes into the string using the chr$(34) function, instead
of using the triple quotes (its just easier for me to read with my bad
eyes).
" ( SELECT Tmp.[EmployeeSSNumber] " & _
Click to expand...
" FROM [REmployee] As Tmp " & _
" WHERE Tmp.REmployee.FileId = " & chr$(34) & CurrentFileId & chr$(34) & _
" GROUP BY Tmp.[EmployeeSSNumber] " & _
Click to expand...
" HAVING Count(Tmp.*) >1 )) " & _
" AND (REmployee.FileId = " & chr$(34) & CurrentFileId & chr$(34) & " )" & _
"ORDER BY EmployeeSSNumber"
Click to expand...


Another thing you might want to try this.

1. Define a string varaible (strSQL)
2. instead of setting your cmd property equal to the code, set the strSQL
equal to your new code
3. Then, on the next line of code enter: debug.print strSQL
4. Step thru the code using the F8 key, so that the code pauses after the
debug.print operation
4. Copy the SQL string from the debug window and paste it in a query. If
it doesn't work, Access will usually highlight where the flaw is in the
code. Then you can go back, modify the code, move your pointer back to the
statement: strSQL = "Select ...", and step thru it again, copying and
pasting the code from the debug window into your querys SQL until it runs
properly.

HTH
Dale


Bob M. said:
The big statement at bottom worked when I had FileId = "fl_u015" but when I
put in variable instead it takes forever and instead of a
RECORDSET of 102 records it yeilds 3 records and is not correct.

Any ideas???

Bob M.

' This works in ado properties: as of 7/13/04

' SELECT EmployeeSSNumber, DupRecordFlag, REmployee.FileId, CompanyID,
RecordType, StateCode, EmployeeFirstName, EmployeeLastName, UnitCode,
EmployeeMiddleName, EmployeeNameSuffix, QuarterEnding, EmployeeGrossWages,
EmployeeTaxableWages, UnemploymentTaxAccountNumber FROM REmployee WHERE
(EmployeeSSNumber In ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp
WHERE FileId = "Fl_u015" GROUP BY [EmployeeSSNumber] HAVING
Count(*)>1 ) ) AND (FileId= "Fl_u015" ) ORDER BY EmployeeSSNumber

' This is not working correctly: as of 7/13/04

' cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, DupRecordFlag, " & _
" UnitCode, EmployeeMiddleName, EmployeeNameSuffix, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding, " & _
" EmployeeGrossWages, EmployeeTaxableWages,
UnemploymentTaxAccountNumber" & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE (REmployee.FileId = """ & CurrentFileId & """)" & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (REmployee.FileId = """ & CurrentFileId & """ )" & _
"ORDER BY EmployeeSSNumber"
Click to expand...
 
The big statement at bottom worked when I had FileId = "fl_u015" but when I
put in variable instead it takes forever and instead of a
RECORDSET of 102 records it yeilds 3 records and is not correct.

Any ideas???

Bob M.

' This works in ado properties: as of 7/13/04

' SELECT EmployeeSSNumber, DupRecordFlag, REmployee.FileId, CompanyID,
RecordType, StateCode, EmployeeFirstName, EmployeeLastName, UnitCode,
EmployeeMiddleName, EmployeeNameSuffix, QuarterEnding, EmployeeGrossWages,
EmployeeTaxableWages, UnemploymentTaxAccountNumber FROM REmployee WHERE
(EmployeeSSNumber In ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp
WHERE FileId = "Fl_u015" GROUP BY [EmployeeSSNumber] HAVING
Count(*)>1 ) ) AND (FileId= "Fl_u015" ) ORDER BY EmployeeSSNumber

' This is not working correctly: as of 7/13/04

' cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, DupRecordFlag, " & _
" UnitCode, EmployeeMiddleName, EmployeeNameSuffix, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding, " & _
" EmployeeGrossWages, EmployeeTaxableWages,
UnemploymentTaxAccountNumber" & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE (REmployee.FileId = """ & CurrentFileId & """)" & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (REmployee.FileId = """ & CurrentFileId & """ )" & _
"ORDER BY EmployeeSSNumber"
 
Try creating the subquery as a separate query, and see if
that works
with a variable, by itself.

Then create the main query off that query, and see if that
works.

That might give you some idea where the trouble is.
 
Try:

cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, DupRecordFlag, " & _
" UnitCode, EmployeeMiddleName, EmployeeNameSuffix, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding, " & _
" EmployeeGrossWages, EmployeeTaxableWages, " & _
" UnemploymentTaxAccountNumber " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE (Tmp.FileId = """ & CurrentFileId & """)" & _
" GROUP BY [EmployeeSSNumber] " & _
" HAVING Count(*)>1 ) ) " & _
" AND (REmployee.FileId = """ & CurrentFileId & """ ) " & _
" ORDER BY EmployeeSSNumber"

Note that I used Tmp.FileID in the WHERE Clause of the SubQuery (5th last
line above) which refers to the Field FileID on the *second copy* of the
Table [REmployee] being aliased as "Tmp". Your use of "REmployee.FileId"
refers to the Field FileID of the *first copy* of the Table [REmployee],
i.e. the copy used by the MainQuery. Hence, the incorrect dataset is
returned.

In Ken's suggestion, he didn't use any qualifier which will default to the
copy of the Table in the SubQuery and therefore his SQL String should also
return the correct dataset.

--
HTH
Van T. Dinh
MVP (Access)




Bob M. said:
The big statement at bottom worked when I had FileId = "fl_u015" but when I
put in variable instead it takes forever and instead of a
RECORDSET of 102 records it yeilds 3 records and is not correct.

Any ideas???

Bob M.

' This works in ado properties: as of 7/13/04

' SELECT EmployeeSSNumber, DupRecordFlag, REmployee.FileId, CompanyID,
RecordType, StateCode, EmployeeFirstName, EmployeeLastName, UnitCode,
EmployeeMiddleName, EmployeeNameSuffix, QuarterEnding, EmployeeGrossWages,
EmployeeTaxableWages, UnemploymentTaxAccountNumber FROM REmployee WHERE
(EmployeeSSNumber In ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp
WHERE FileId = "Fl_u015" GROUP BY [EmployeeSSNumber] HAVING
Count(*)>1 ) ) AND (FileId= "Fl_u015" ) ORDER BY EmployeeSSNumber

' This is not working correctly: as of 7/13/04

' cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, DupRecordFlag, " & _
" UnitCode, EmployeeMiddleName, EmployeeNameSuffix, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding, " & _
" EmployeeGrossWages, EmployeeTaxableWages,
UnemploymentTaxAccountNumber" & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE (REmployee.FileId = """ & CurrentFileId & """)" & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (REmployee.FileId = """ & CurrentFileId & """ )" & _
"ORDER BY EmployeeSSNumber"
Click to expand...
 
Back
Top