Test all records in a many to one linked records

[Phil Abrahamsen]

Access 2002:

Example:
Master record: ID#, Name
Linked records: ID#, Member year
If a member for the last 3 years you have 3 linked records withmember year =
2002, 2003, and 2004.
I want to find all ID#'s that were members in 2003 but NOT 2004.
A query for member year = 2003 and <> 2004 yields ALL 2003 records because
the 2003 record = 2003 and NOT 2004.
How can the query look at all the linked records at the same time for a
valid test?

Phil Abrahamsen
ph**@bowersoc.com

--
----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

[Douglas J. Steele]

In essence, you need to create subselects of the 2003 members and the 2004
members and join them.

The following SQL is untested aircode:

SELECT A.ID, A.[Name]
FROM
(SELECT ID, [Name]
FROM Master INNER JOIN Linked
ON Master.ID = Linked.ID
WHERE Linked.MemberYear = 2003) AS A
LEFT JOIN
(SELECT ID, [Name]
FROM Master INNER JOIN Linked
ON Master.ID = Linked.ID
WHERE Linked.MemberYear = 2004) AS B
ON A.ID = B.ID
WHERE B.ID IS NULL
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Phil Abrahamsen" <ph**@abrahamsens.com> wrote in message
news:d1********@dispatch.concentric.net...

Access 2002:

Example:
Master record: ID#, Name
Linked records: ID#, Member year
If a member for the last 3 years you have 3 linked records withmember year
=
2002, 2003, and 2004.
I want to find all ID#'s that were members in 2003 but NOT 2004.
A query for member year = 2003 and <> 2004 yields ALL 2003 records because
the 2003 record = 2003 and NOT 2004.
How can the query look at all the linked records at the same time for a
valid test?

Phil Abrahamsen
ph**@bowersoc.com

--
----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com