INNER JOIN - Need Records Not in Table #2

[VaMPiREX]

I have two tables, let's say table one is CONTACTS and table #2 AMTPAID.
CONTACTS has a ContactID for the primary key and AMTPAID contains all the
money paid and is related to CONTACTS by CONTACTID. I need to create a JOIN
so that I get all the records in CONTACTS who have never paid anything.
Which means that they wouldn't have any entries in the AMTPAID table. I am
an Access newbie but there seems that there would be a way to do this. Maybe
a JOIN is the wrong way to go. I would appreciate any help on this.

DJ

 

[Dirk Goldgar]

I have two tables, let's say table one is CONTACTS and table #2
AMTPAID. CONTACTS has a ContactID for the primary key and AMTPAID
contains all the money paid and is related to CONTACTS by CONTACTID.
I need to create a JOIN so that I get all the records in CONTACTS who
have never paid anything. Which means that they wouldn't have any
entries in the AMTPAID table. I am an Access newbie but there seems
that there would be a way to do this. Maybe a JOIN is the wrong way
to go. I would appreciate any help on this.

A join is the right way, all right. What you need is called a
"frustrated outer join" -- you create a query that left-joins CONTACTS
to AMTPAID (so you get all records from CONTACTS and only the matching
records from AMTPAID), and then selects only those records from the
resulting set that do not have matching AMTPAID fields. Such a query
might look like this:

SELECT CONTACTS.*
FROM
CONTACTS
LEFT JOIN
AMTPAID
ON CONTACTS.CONTACTID = AMTPAID.CONTACTID
WHERE AMTPAID.CONTACTID Is Null;