Help Query problem

[Harrie Simmes]

Hi,

I 'm new to the Access world and I have the following query problem.

I have a table with members to a club.
The members renew each year their membership.

I have a member table and a one to many relation to a membershiptable filled
with entries for each year).
So the membershiptable looks like this (sorted on member#).

member1 2000
member1 2001
member1 2002
member1 2003
member2 2000
member2 2001 (member2 quits the club at the end of 2001)
member3 2000
member3 2001
member3 2002 (member3 quits the club at the end of 2002 now on)
member4 2002 (member4 is new to the club from 2002 on)
member4 2003


Now I want a query wich gives me the me the members that were quiting the
club in a certain year ...for example who quited the club at the end of
2001.

In this smal example that is member2.

Idem the lookalike question who is new to the club in 2002. In this example
member4.

I hope for some answers beacause the question is nagging me already a few
days.

Kind regards,

Harrie

 

[Andrew Smith]

Try this SQL (it uses a subquery in the WHERE clause):

SELECT DISTINCT MemberID
FROM tblMembership
WHERE (SELECT Top 1 MembershipYear FROM tblMembership as A WHERE
A.MemberID = tblMembership.MemberID ORDER BY MembershipYear Desc =[Enter
year of leaving:]

To get this to work replace tblMembership with the name of your membership
table, MembershipYear with the name of your year field, and MemberID with
the name of your member ID field.

[Enter year of leaving] is a parameter that you will be asked for when the
query is run. You can change this for the message that you would like to
see.

To change to show year of joining just sort the subquery in ascending order
rather than descending order (delete the word "Desc", or replace it with
"Asc").