Query as Criteria

[James Stephens]

I am sure this is simple but I can't seem to get it to work. I have two
queries, the first one with 1 field (SSN) there will me multiple records in
this query, the second one has 7 fields, one of which is (SSN). I want to
show all the results from query two where the SSN is not one of the SSN
listed in Query 1. I have tried to put this in the criteria as
<>[Query1]![SSN] but it prompts me for the value instead of looking it up
from the query.

Any idea how to make this work,

Thanks

Jim

 

[Ken Snell [MVP]]

You need to join the two queries in a new query this way:

SELECT Query2.*
FROM Query2 LEFT JOIN Query1
ON Query2.SSN = Query1.SSN
WHERE Query1.SSN Is Null;

 

[James Stephens]

Thanks alot, that worked perfectly

You need to join the two queries in a new query this way:

SELECT Query2.*
FROM Query2 LEFT JOIN Query1
ON Query2.SSN = Query1.SSN
WHERE Query1.SSN Is Null;

--

Ken Snell
<MS ACCESS MVP>

I am sure this is simple but I can't seem to get it to work. I have two
queries, the first one with 1 field (SSN) there will me multiple records
in
this query, the second one has 7 fields, one of which is (SSN). I want to
show all the results from query two where the SSN is not one of the SSN
listed in Query 1. I have tried to put this in the criteria as
<>[Query1]![SSN] but it prompts me for the value instead of looking it up
from the query.

Any idea how to make this work,

Thanks

Jim