Access Help: Date Querries or Calculation

[carolyn]

I have over 500 members. I have created two tables. a have a membership table with the following fiels:

Members
Last Name	Member Type ID	Date Joined	Member Dues
Barr	Friend	1/6/2000	$35.00

I also have a Membership Level table with the following fields.

Member Types
Member Type ID	Member Type	Member Dues
1	Friend	$35.00
2	Contributing Friend	$100.00
3	Sustaining Friend	$250.00
4	2012 Member Level 1	$25.00
5	2012 Member Level 2	$50.00
6	2012 Member Level 3	$75.00

How would I create a query in Access that will let me know when the membership expires based on the following $35=1year membership - $100 = 2years - $250 = 3years? I do not know where to begin.

Members register throughout the year. I would like to be notified monthly or weekly when the membership expires based on the number of years the member paid. I also would like to sort member based on their contribution level.

[June7]

Names are not good unique identifiers. Could have many Smith members.

This could get complicated.

Need a table that has payment info - MemberID, PaymentDate, MemberType

Include another field in MemberTypes for the period in years (1, 2, 3, etc).

Need a query that will pull the most recent payment record for each member.

Join that query to the MemberTypes table.

Calculate the renewal date using the most recent payment date and the period field.

Compare the renewal date with current date.

Sort that query by MemberType.

[ssanfu]

You show "Member Type ID" in table "Members" as text, but in table "Member Types", "Member Type ID" is a number. Are you using Lookup FIELDS in you tables?

See
The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm and

The Evils of Lookup Fields in Tables

http://access.mvps.org/access/lookupfields.htm


How do you know the "when the membership expires based on the number of years the member paid."?