How to select non-entries

[cheftim]

Hello,

Please help me get my mind around this problem. Although I have read most posts in the forum over the past several months, I can't even understand how to search for this topic.

I have two tables: Events and Payments.

EVENTS
Event_ID
Event_Name


PAYMENTS
Payment_ID
Event_ID
Payment_Amount

There is no problem doing a simple select query to make a list of events that have payments.

My problem is I need to create a list of events that have no payments. Any idea how to structure this?

Many thanks in advance for your assistance.

t

 

[alastair69]

Can you attach a copy of your Database and i will have a look for you

 

[The_Doc_Man]

Basically, you need to count the payment IDs for each Event ID. Then find all event IDs for which that count is zero. That involves a query that might look like this:

SELECT EVENT_ID, EVENT_NAME FROM EVENTS WHERE
DCOUNT( "[Payment_ID]", "PAYMENTS", "[Event_ID]=" & CStr([Event_ID]![Events] ) = 0;

It will something along those lines. Look up DCount in Access Help. Also search this forum for examples of DCount usage.

 

[pbaldy]

You might also try this to see if it serves your needs:

SELECT EVENTS.Event_ID
FROM EVENTS LEFT JOIN PAYMENTS ON EVENTS.Event_ID = PAYMENTS.Event_ID
WHERE PAYMENTS.Event_ID Is Null

 

[Rabbie]

Just use the Find unmatched query wizard

 

[cheftim]

Rabbie

Thanks so much - I didn't know that was in there.

It works great!

 

[neileg]

The wizard will have generated SQL that looks remarkable like pbaldy's answer!