SQL query will not work with subquery 2

[TNN]

Can someone please tell me why this SQL statement will not work???

SELECT FNAME,MIDDLE,LNAME
FROM BASSPERS
WHERE BASSPERS.EMPNO=(SELECT DISTINCT EMPNO FROM EMPEARNPERWAGE
WHERE PYRLNO=(SELECT LASTPYRLNO FROM LASTPYRLNO))

LASTPYRLNO is a field in a table LASTPYRLNO.

The subquery (SELECT DISTINCT..... works good by itself and returns a single column of EMPNO's.

TNN, Tom


TOM

http://WWW.TNPAYROLL@AOL.COM

 

[JonFer]

You need semicolons at the end of the subqueries.

For the EMPNO criteria, you should use the In operator instead of = because you are getting multiple EMPNOs back.

For the PYRLNO criteria, if there are multiple LASTPYRLNO's, use Max() or something in the subquery to return a single value.

 

[dhookom]

Good points from JonFer. I don't think you need the semicolons in subqueries as I have never used them.

Duane
MS Access MVP

 

[TNN]

JonFer,
Using the "IN" operator worked. What is it then, you use the = sign only when the resultant recordset is one record with a single field????

There is only a single LASTPYRLNO.

Thank You very much, TNN,Tom

PS: The semicolons were not necessary.



TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

dhookom,
Your right, semicolons not necessary. The "IN" operator worked.

Thank You for the input, TNN, Tom


TOM

http://WWW.TNPAYROLL@AOL.COM