Need help with SQL code

[Guest]

I have a database, MEETINGS, with two tables. The first table, PERSONS, has information about people who have attended meetings. The second table, ATTENDEES, has information about who attended each meeting. There is a row for each attendee/meeting pair. The two important fields in the ATTENDEES table are the PersonID and the MeetingDate. I set up the database in this way to normalize it.

I want to SELECT PersonIDs for those persons that attended a meeting on date A but did not attend the meeting on date B. I do not want those people that attended both meetings or only the meeting on date B.

I have tried all sorts of JOINs but to no avail. How do I write a query that will give me what I need?

 

[Guest]

I realize that I defined my problem incorrectly. I want to SELECT PersonIDs for those persons that attended a meeting on either date A or date B but did not attend both meetings. I could not get a JOIN to give me what I need.

 

[Douglas J. Steele]

That's a much simpler problem!

SELECT PersonID
FROM ATTENDEES
WHERE MeetingDate IN (#2004/06/30#, #2004/07/01#)
GROUP BY PersonID
HAVING Count(*) = 1

For what it's worth, I'd suggest having a third table MEETINGS and assigning
a MeetingID for each meeting. The ATTENDEES table would then have MeetingID
in it, not MeetingDate. This will make it simpler if you ever have multiple
meetings on the same day.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I realize that I defined my problem incorrectly. I want to SELECT

PersonIDs for those persons that attended a meeting on either date A or date
B but did not attend both meetings. I could not get a JOIN to give me what
I need.has information about people who have attended meetings. The second table,
ATTENDEES, has information about who attended each meeting. There is a row
for each attendee/meeting pair. The two important fields in the ATTENDEES
table are the PersonID and the MeetingDate. I set up the database in this
way to normalize it.date A but did not attend the meeting on date B. I do not want those people
that attended both meetings or only the meeting on date B.that will give me what I need?

 

[John Spencer (MVP)]

A Totals query like the following might do the job


SELECT PersonIDs
From Attendees
WHERE MeetingDate in (#1/2/04#,#1/5/04#)
GROUP BY PersonIDS
HAVING COUNT(PersonIDs) > 1

That should give you the PersonIDs where the person attended a meeting on one
but not both dates.

If you are doing this is the query grid, select View: Total from the menu

Add PersonIDs into the grid two times and MeetingDate once

Under the first personIDs select Group by
Under the second personIds select Count
Under MeetingDate select WHERE

In the criteria,
put >1 under the second PersonIDs
Put In(YourFirstDate,YourSecondDate) under Meeting date.

If you need to do this and want more information returned from the PERSONS table
then you can join this query to your Persons table in another query or you can
use this query as a subquery. See SQL Statement below.

SELECT Persons.*
FROM Persons
WHERE Persons.PersonIDs IN
(SELECT A.PersonIDs
From Attendees As A
WHERE A.MeetingDate in (#1/2/04#,#1/5/04#)
GROUP BY A.PersonIDS
HAVING COUNT(A.PersonIDs) > 1)

 

[Guest]

Thanks, it worked like a charm!

That's a much simpler problem!

SELECT PersonID
FROM ATTENDEES
WHERE MeetingDate IN (#2004/06/30#, #2004/07/01#)
GROUP BY PersonID
HAVING Count(*) = 1

For what it's worth, I'd suggest having a third table MEETINGS and assigning
a MeetingID for each meeting. The ATTENDEES table would then have MeetingID
in it, not MeetingDate. This will make it simpler if you ever have multiple
meetings on the same day.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)




PersonIDs for those persons that attended a meeting on either date A or date
B but did not attend both meetings. I could not get a JOIN to give me what
I need.
has information about people who have attended meetings. The second table,
ATTENDEES, has information about who attended each meeting. There is a row
for each attendee/meeting pair. The two important fields in the ATTENDEES
table are the PersonID and the MeetingDate. I set up the database in this
way to normalize it.
date A but did not attend the meeting on date B. I do not want those people
that attended both meetings or only the meeting on date B.
that will give me what I need?