Query as Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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;
 
Thanks alot, that worked perfectly

Ken Snell said:
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>


James Stephens said:
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
Click to expand...
Click to expand...
 
Back
Top