Records in A but not in B, with multiple IDs

[JonFleming]

In Access, I have a table of people, a table of mailings, a table of people to which particular mailings have been sent, and a table of people to which particular mailings are going to be sent. (There's a PersonID and a MailingID in the obvious places, but here I'm working with PersonID and the mailing description). I have a simple query which returns two columns, a personID and a the description of a mailing which _could_ be sent or _has_ _been_ sent to that person:

Query A:
PersonID Mailing
4 QOH Flyer
4 Testing
5 QOH Flyer
5 Testing
6 QOH Flyer
6 Testing

(That is, there are three people and two mailings in the database, and this is just all combinations of those fields).

I have another simple query which returns a personID and a description of a mailing, but in this case it only contains records for which the person has been sent this mailing or is already on a list to be sent this mailing:

Query B:
PersonID Mailing
4 QOH Flyer
4 Testing
5 QOH Flyer
6 QOH Flyer
6 Testing

Now, of course the really interesting information is "what combinations of people and mailings have we not yet mailed or listed to be mailed?" or "what records appear in query A but not in query B?". We can see immediately that the desired result is:

PersonID Mailing
5 Testing

The classic way to do this sort of thing is:

SELECT A.PersonID, A.Mailing, B.PersonID, B.Mailing
FROM A LEFT JOIN B
ON A.PersonID = B.PersonID
WHERE B.PersonID IS NULL;

but this doesn't work; I think it's because the PersonID appears multiple times in A and/or B.

So, how?

 

[PHV]

ON A.PersonID = B.PersonID AND A.Mailing = B.Mailing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JonFleming]

Ah, that does work. Now I need to figure out why.

Thanks.