Comparing Queries?

[Guest]

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.

It's as though I need to run the query for a single attendee twice and then
compare them to return only those events which appear in both lists. I
imagine to do this for three or four attendees is even more involved. Any
help constructing this would be appreciated.

 

[Marshall Barton]

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

 

[Guest]

:> 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

Thank you for responding.

The way I am looking at this need is that I would need to enter the names of
the two attendees (John and Mary). Having entered their names, the query
would return the events they both need to attend. I do not see where the
query ever inputs the attendees' names above.

 

[Marshall Barton]

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

Thank you for responding.

The way I am looking at this need is that I would need to enter the names of
the two attendees (John and Mary). Having entered their names, the query
would return the events they both need to attend. I do not see where the
query ever inputs the attendees' names above.

No, it doesn't try to do that. It looks for events that no
one has attended.

If you want to look for a small set of specific attendees,
you can add a WHERE clause:

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

 

[Guest]

If you want to look for a small set of specific attendees,
you can add a WHERE clause:

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

Once again, thank you for your assistance.

No records were returned. If I understand the SQL correctly, the query is
looking for all the events not attended and then it is looking at a sub-set
of those which apply for John and Mary.

It is quite possible that the event may have been attended by Dave, but not
by John and Mary.

Of course, I may be misunderstanding what is going on. I don't understand
all this very well, but I learn with everything I read here.

 

[Marshall Barton]

No records were returned. If I understand the SQL correctly, the query is
looking for all the events not attended and then it is looking at a sub-set
of those which apply for John and Mary.

It is quite possible that the event may have been attended by Dave, but not
by John and Mary.

Of course, I may be misunderstanding what is going on. I don't understand
all this very well, but I learn with everything I read here.

Well, that's not what I expected. This isn't VBA, but SQL
can be pretty frustrating too ;-)

Here's my next(?) shot at what I think(?) you want:

SELECT Events.EventName,
Events.EventType,
Events.EventState,
Count(X.Attendee) AS CountOfKey
FROM Events LEFT JOIN
(SELECT EventName, Attendee
FROM Attendance
WHERE Attendee IN ("John","Mary")
) AS X
ON Events.EventName = X.EventName
GROUP BY Events.EventName,
Events.EventType,
Events.EventState
HAVING Count(X.Attendee) = 0

 

[Guest]

SELECT Events.EventName,
Events.EventType,
Events.EventState,
Count(X.Attendee) AS CountOfKey
FROM Events LEFT JOIN
(SELECT EventName, Attendee
FROM Attendance
WHERE Attendee IN ("John","Mary")
) AS X
ON Events.EventName = X.EventName
GROUP BY Events.EventName,
Events.EventType,
Events.EventState
HAVING Count(X.Attendee) = 0

Thank you for your patience with me.

This did not return any records either. I feel bad that all I do is ask and
cannot really do much. I have played with various versions and nothing
works. I think there is a problem with selecting Count(X.Attendee) AS
CountOfKey and then trying to assign two attendees' names to that.

I think maybe I have not been clear in the situation. My terminology for
the functions is most likely limited; I just don't know what needs to be done
in Access.

I have two tables. One records all the events, the other records all the
attendance of those events.

There are many events in which the people need to attend. I can easily
query the Attendance table to determine which events John still needs to
attend. To do that I use the following:
SELECT EventName, EventType, EventState
FROM Events
WHERE (((EventName) Not In (select EventName from
Attendance where Attendee =[Attendee?]));

I could run that query for John and then again for Mary. I would have two
lists of events they each need to attend.

If John and Mary pair up to attend some events, they would need to compare
their lists. That is certainly able to happen. But what I would like to do
is to produce a single list that displays all the events that both of them
need to attend, excluding any event which one/both have already attended.

I hope that might present this situation in a clearer light.

Once again, I cannot thank you enough for your help. Novices like me are
ever grateful for the help of you who are more experienced. Thanks!

 

[Marshall Barton]

This did not return any records either. I feel bad that all I do is ask and
cannot really do much. I have played with various versions and nothing
works. I think there is a problem with selecting Count(X.Attendee) AS
CountOfKey and then trying to assign two attendees' names to that.

I think maybe I have not been clear in the situation. My terminology for
the functions is most likely limited; I just don't know what needs to be done
in Access.

I have two tables. One records all the events, the other records all the
attendance of those events.

There are many events in which the people need to attend. I can easily
query the Attendance table to determine which events John still needs to
attend. To do that I use the following:
SELECT EventName, EventType, EventState
FROM Events
WHERE (((EventName) Not In (select EventName from
Attendance where Attendee =[Attendee?]));

I could run that query for John and then again for Mary. I would have two
lists of events they each need to attend.

If John and Mary pair up to attend some events, they would need to compare
their lists. That is certainly able to happen. But what I would like to do
is to produce a single list that displays all the events that both of them
need to attend, excluding any event which one/both have already attended.

Yes that is clearer. However, even though I wasn't sure
before, that is what my last query is supposed to do.

Your query using the Not In (... will work for one person,
but a different approach is required for multiple people.
It would be more efficient if you used a Left Join and
Where Attendee Is Null

The way my latest query works is that the subquery collects
a list of all the events that either John or Mary attended.
The list of all events (Left Join) is merged the list of
John and Mary attendance. The Group By allows us to Count
the number of people that attended each event (in this case
0, 1 or 2). The Having clause then selects only those
events that have a Count of 0 (i.e. neither John nor Mary).

I did go to the trouble of setting up two tables similar to
yours and tested it to make sure it worked in my situation.

Are you sure your data is such that you should find some
event for those two? Does the Attendee field in the table
really contain just John orMary??

Is there a chance that I've used the wrong field somewhere
or that you made a mistake copying the query into your
environment???

If none of those thoughts bring any light on the problem,
let's hope someone else jumps in and straightens us out.

 

[Guest]

that is what my last query is supposed to do.

I did go to the trouble of setting up two tables similar to
yours and tested it to make sure it worked in my situation.

I returned to this this evening. I made copies of the tables I was working
with and named everything exactly as we have been using. I copied the query
and all worked.

Then I went back and substituted the names from my actual tables. Again
nothing. I went through each character, one-by-one. I found a minor mistake
on my part. That had crapped out the entire thing. I am so sorry. Somehow
I had missed it previously.

THANK YOU.

One last thing (which is not absolutely necessary, but would be nice) . . .
In some of my queries I am able to prompt for the Attendee name in the
Design View by adding something like [Attendee?] in the Criteria area.

With this query, however, the Attendee is not viewable in the Design View
area and my attempts to substitute
WHERE Attendee IN ("John","Mary")

with

WHERE Attendee IN ([Attendee1?], [Attendee2?]) (or variations of that)

have failed.

Is it possible to prompt for these values?

Once again, thank you for your help. Your hand holding through this has
helped me tremendously.

 

[Marshall Barton]

that is what my last query is supposed to do.

I did go to the trouble of setting up two tables similar to
yours and tested it to make sure it worked in my situation.

I returned to this this evening. I made copies of the tables I was working
with and named everything exactly as we have been using. I copied the query
and all worked.

Then I went back and substituted the names from my actual tables. Again
nothing. I went through each character, one-by-one. I found a minor mistake
on my part. That had crapped out the entire thing. I am so sorry. Somehow
I had missed it previously.

THANK YOU.

One last thing (which is not absolutely necessary, but would be nice) . . .
In some of my queries I am able to prompt for the Attendee name in the
Design View by adding something like [Attendee?] in the Criteria area.

With this query, however, the Attendee is not viewable in the Design View
area and my attempts to substitute
WHERE Attendee IN ("John","Mary")

with

WHERE Attendee IN ([Attendee1?], [Attendee2?]) (or variations of that)

have failed.

Is it possible to prompt for these values?

Once again, thank you for your help. Your hand holding through this has
helped me tremendously.

Whew!

Glad we got it working, in spite of all the frustrations
;-)

I would expect your attempt to use prompt parameters to
work. Are you sure you didn't have another misspelling
somewhere? ;-)

Actually, I would approach generalizing this query
differently. First, create a form to manage the situation
and display the results of the query. In the form's Header
section, add a MultiSelect list box with its RowSource set
to a query to get the names from the Attendees table (or
where ever you keep the master list of people's names).
Also add a command button to process the query.

In the detail section add bound text boxes to display the
query's Event's data. Set the form to display in Continuous
mode.

The code behind the button's Click event would be along the
lines of this air code (untested):

Const strFirstPart As String = _
"SELECT Events.EventName, " _
& "Events.EventType, " _
& "Events.EventState, " _
& "Count(X.Attendee) AS CountOfKey " _
& "FROM Events LEFT JOIN " _
& "(SELECT EventName, Attendee " _
& "FROM Attendance " _
& " WHERE Attendee IN ("

Const strLastPart As String = ") " _
& ") AS X " _
& "ON Events.EventName = X.EventName " _
& "GROUP BY Events.EventName, " _
& "Events.EventType, " _
& "Events.EventState " _
& "HAVING Count(X.Attendee) = 0 "
Dim strNames As string
Dim varItem As Variant

With Me.lstAttendees
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strNames = ", """ & .ItemData(varItem) & """"
Next varItem
Me.RecordSource = strFirstPart & _
Mid(strNames, 3) & strLastPart
Else
MsgBox "Please select at least one person"
End If
End With