How do I create a query to include a value and exclude a value

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

Guest

I want to create a query to show me all records with a Payment type = "event"
while excluding records with a Payment type of "Donation".
 
Just the word "event" in the criteria of the Payment type field should do the
trick unless your records say something like:

"donation for the charity event"
 
There are multiple payment records. Some may be coded as event records and
some may be coded as donations. I want a list of everyone who has made a
payment for an event but never made a donation. I am trying to use "NOT" and
can't figure out how to do it.
 
There are multiple payment records. Some may be coded as event records and
some may be coded as donations. I want a list of everyone who has made a
payment for an event but never made a donation. I am trying to use "NOT" and
can't figure out how to do it.
Click to expand...

The problem then is that you're applying the criteria to DIFFERENT
RECORDS. There is no single record in the table that contains the
needed information.

You may need to use a NOT EXISTS query:

SELECT whateverfields
FROM donortable
INNER JOIN payments
ON donortable.donorID = payments.donorID
WHERE typefield = "Event"
AND
NOT EXISTS
(SELECT DonorID FROM payments AS X
WHERE X.DonorID = donortable.DonorID
AND X.type = "Donation");

SInce you didn't post any details of your table structure I'm groping
in the dark - but the NOT EXISTS subquery should be adapatable.

John W. Vinson[MVP]
 
Back
Top