How to call a query from within another query

[wgma]

I have two queries. One returns a list of numbers and I would like the other one to use that list in the where statement.

Here is the query

SELECT WGMAHIST_TIMEDTY.TDYSSN, WGMAHIST_TIMEDTY.TDYLCL, WGMAPROD_EMPLOYM.EMP1ST, WGMAPROD_EMPLOYM.EMPMID, WGMAPROD_EMPLOYM.EMPLST, WGMAPROD_EMPLOYM.EMPSUF, SUM(WGMAHIST_TIMEDTY.TDYFND) AS TotHrs
FROM WGMAHIST_TIMEDTY INNER JOIN WGMAPROD_EMPLOYM ON WGMAHIST_TIMEDTY.TDYSSN=WGMAPROD_EMPLOYM.EMPSSN
WHERE (((WGMAHIST_TIMEDTY.TDYLCL)=24) AND (NOT (WGMAHIST_TIMEDTY.TDYOCC) In (122,124,321)) AND ((WGMAHIST_TIMEDTY.TDYTDT) Between 20061001 And 20070930)) AND WGMAHIST_TIMEDTY.TDYSSN IN (
qryCertifiedTruckDriversAllLocalsSSNOnly
)
GROUP BY WGMAHIST_TIMEDTY.TDYSSN, WGMAHIST_TIMEDTY.TDYLCL, WGMAPROD_EMPLOYM.EMP1ST, WGMAPROD_EMPLOYM.EMPMID, WGMAPROD_EMPLOYM.EMPLST, WGMAPROD_EMPLOYM.EMPSUF
ORDER BY WGMAPROD_EMPLOYM.EMPLST, WGMAPROD_EMPLOYM.EMP1ST;

Here is the query qryCertifiedTruckDriversAllLocalsSSNOnly:

SELECT WGMAHIST_TIMEDTY.TDYSSN
FROM WGMAHIST_TIMEDTY INNER JOIN WGMAPROD_EMPLOYM ON WGMAHIST_TIMEDTY.TDYSSN=WGMAPROD_EMPLOYM.EMPSSN
WHERE (((WGMAHIST_TIMEDTY.TDYLCL) IN (24,28)) AND ((WGMAHIST_TIMEDTY.TDYOCC) In (122,124,321)) AND ((WGMAHIST_TIMEDTY.TDYTDT) Between 20061001 And 20070930))
GROUP BY WGMAHIST_TIMEDTY.TDYSSN;


If I try to run the top query as it is I am prompted for a parameter of qryCertifiedTruckDriversAllLocalsSSNOnly. I know that there is a way to use the query like I have it above.

I have tried to insert the SQL from the second query into the first but it goes on forever.

Can someone please help?

Thanks.

 

[pbaldy]

I think the correct syntax would be:

AND WGMAHIST_TIMEDTY.TDYSSN IN (SELECT TDYSSN FROM qryCertifiedTruckDriversAllLocalsSSNOnly)

But you could probably join them and get better performance.