Give me code please

[Guest]

Can you please give me the code that selects all the ID's from 'atnCOMMITTEEMEMBER' that are not in 'atnMEETINGATTENDANCE'.

The two tables are

Table1: atnCOMMITTEEMEMBE
Field1: CommitteeID - P
Field2: YearID
Field3: ID
Field4: CommitteePositionI

Table2: atnMEETINGATTENDANC
Field1: MeetingID - P
Field2: ID
Field3: AttendanceStatusI
Field4: Note

Thanks

Eric

 

[John Viescas]

SELECT DISTINCT atnCommitteeMember.CommitteeID
FROM atnCommitteeMember
LEFT JOIN atnMeetingAttendance
ON atnCommitteMember.[ID#] = atnMeetingAttendance.[ID#]
WHERE atnMeetingAttendance.MeetingID IS NULL

... assuming that the ID# is the field that links a particular member to a
particular meeting.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Can you please give me the code that selects all the ID's from

'atnCOMMITTEEMEMBER' that are not in 'atnMEETINGATTENDANCE'.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't say which ID so I'll use ID#.

SELECT ID#
FROM atnCommitteeMember
WHERE ID# NOT IN (SELECT ID# FROM atnMeetingAttendance)

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDOqTIechKqOuFEgEQKmmACcDk7QXQPzWgT05HFYOjwtAANJeH0AoJXa
bq1m2/FhfZ7mK6DgEOXXQcDj
=pypW
-----END PGP SIGNATURE-----

 

[John Vinson]

Can you please give me the code that selects all the ID's from 'atnCOMMITTEEMEMBER' that are not in 'atnMEETINGATTENDANCE'.

The two tables are:

Table1: atnCOMMITTEEMEMBER
Field1: CommitteeID - PK
Field2: YearID
Field3: ID#
Field4: CommitteePositionID

Table2: atnMEETINGATTENDANCE
Field1: MeetingID - PK
Field2: ID#
Field3: AttendanceStatusID
Field4: Notes

Thanks,

Eric

Create a Query joining atnCOMMITTEEEMEMBER to atnMEETINGATTENDANCE by
ID# (note that # is risky in fieldnames since it's a date delimiter!)

Select the Join line and change it to Option 2 - "Show all records in
atnCOMMITTEEEMEMBER and matching records in atnMEETINGATTENDANCE".
This will show you all the members, and what meetings they've
attended; if they haven't attended any the Attendance fields will be
NULL.

Select ONLY the ID# field from atnMEETINGATTENDANCE and put a
criterion of IS NULL. This will restrict the list to those slackers
who abused the privilege of membership and disdained to attend any
meetings.

 

[John Viescas]

MG-

Your answer is correct, but JET optimizes NOT IN very poorly. See the
solutions using Outer Join to achieve the same result much faster - posted
by me and the "other" JohnV (Vinson).

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[MGFoster]

MG-

Your answer is correct, but JET optimizes NOT IN very poorly. See the
solutions using Outer Join to achieve the same result much faster - posted
by me and the "other" JohnV (Vinson).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Right. Realized it immediately I saw your solution. I tend to get a
little subquery happy sometimes.

Rgds,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDUPaYechKqOuFEgEQKtpQCg3IPHTThnxUMX/fQepE0m36AhSNQAnil5
FGVocchvN0U2dKaNS0G39I5y
=r7Ih
-----END PGP SIGNATURE-----