Repost: Membership renewal query

[Max]

I have an Access 2000 database being used to keep track of membership for a
non-profit organization. All memberships expire at the same time each year,
so I have set it up as follows:

tblMembers: contains names, addresses, etc. (primary key: MemberID)
tblYears: contains a list of membership years (2002-2003, 2003-2004,
2004-2005)
tblMemberships: contains an entry for each member for each paid year
(fields: MembershipID, MemberID, Year, and some stuff that's useful to us
for tracking purposes)

Using this, it is very easy to query for all current members. However, I
have been unable to figure out how to query for members who need to renew
(i.e., members who paid for 2003-2004 but not for 2004-2005).

If anyone has a suggestion, I'd appreciate it. Thanks in advance.

=Max=

 

[Allen Browne]

A subquery is probably the easiest way to identify those who have not paid
for 2004-5:

SELECT MemberID FROM tblMemberships
WHERE ([Year] = '2003-2004') AND NOT EXISTS
(SELECT MembershipID FROM tblMemberships AS Dupe
WHERE Dupe.MemberID = tblMemberships.MemberID
AND Dupe.[Year] = '2004-2005' ) ;

That asks for those who had membership in 2003-4, but do not have it for
2004-5.