Re: Comparing Queries?

From: Marshall Barton (marshbarton_at_wowway.com)
Date: 01/30/05 

Date: Sun, 30 Jan 2005 12:53:26 -0600

Frustrated with VBA wrote:

>I have a table (Events) which holds the following:
>EventName, EventType, EventState, and some other fields
>
>I have another table (Attendance) which holds:
>EventDate, EventName, Attendee
>
>I have created some queries based on this table. One is to determine which
>events any particular attendee still needs to complete. In its simpliest
>form, this is the code for that (I stripped some of the fields that aren't
>relevant to this):
>
>SELECT EventName, EventType, EventState
>FROM Events
>WHERE (((EventName) Not In (select EventName from
>Attendance where Attendee =[Attendee?]));
>
>What I desire to do is create a similar query that will return all the
>events that two (three or four) attendees need, but excluding those that not
>all need.
>
>Joe has attended event1, event2, event3
>Mary has attended event2, event4, event5, event6
>
>The query should return event7, event8, event9 only since both need them.

The way I read your question, I think you want a query like
this:

SELECT Events.EventName,
                                Events.EventType,
                                Events.EventState,
                                Count(Attendance.Attendee) AS CountOfKey
FROM Events LEFT JOIN Attendance
        ON Events.EventName = Attendance.EventName
GROUP BY Events.EventName,
                                Events.EventType,
                                Events.EventState
HAVING Count(Attendance.Attendee) = 0 

-- 
Marsh
MVP [MS Access]