Query as Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Stephens - 22 Nov 2005 17:21 GMT

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

Reply to this Message

Ken Snell [MVP] - 22 Nov 2005 17:59 GMT

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;

Signature

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
[quoted text clipped - 10 lines]
>
> Jim

Reply to this Message

James Stephens - 22 Nov 2005 18:22 GMT

Thanks alot, that worked perfectly

> You need to join the two queries in a new query this way:
>
[quoted text clipped - 17 lines]
> >
> > Jim

Reply to this Message