Help with a 2 table query

[SketchEtch]

I work for an organization where we have a access database of contacts, in a table called individual_contacts. At any time, a contact can elect to become a member of our organization. We have several memberhip levels, stored in a table with two fields, level_id, level_name, and the table is member_levels. When someone decides to become a member for a year, say 2001, we create a relational record in a table called contact_history that contains the fields contact_id(foreign key), member_level(foreign key), and amount_paid. This db tracks all such memberships from 1998 on.

Simple enough.

OK, every year we want to send a new membership application to past members, whether they were a member one year, or all years, or any variation in between, as long as they were members at levels 3, 4, 5, or 11 during one of the years in their history.

I want to create an access report that will be this application, and print out a copy for each member that lists thier info as we have it on file, and a list of their membership history. In other words, the report will be a page for each person, and will look similar to this:

Brad Johnson
123 Oak Circle
Tampa FL 33636
(813) 987-6543

MEMBERSHIP HISTORY

1998 - $200
1999 - $0
2000 - $200
2001 - $135

This will be followed by the new membership app i will create, but that will not be dynamic. All that needs to be dynamic is the history, and their info.

How do I do this in an access report? It needs to list their complete histry out, so if they were only a level 5 member in 2001, for 1998, 1999, and 2000 it needs to list $0.

If anyone can help with this I will be eternally grateful!

If you ened the db to help, please ask!

Thanks in advance.

 

[braindead2]

Which table holds the date that they became a member
you say
"we create a relational record in a table called contact_history that contains the fields contact_id(foreign key), member_level(foreign key), and amount_paid."

which table holds the date that this membership covers?
One not so Quick and dirty option is on the report combine IIF() and Dlookup functions
kinda like this

Label.caption = 1998
txt1998 = iif(dlookup("amt","table","year(memberdate) = 1998)>0,dlookup("amt","table","year(memberdate),"0&quot
then do the same for 1999,2000,2001
this is certainly not the best option but is the one thing that pops into my mind based on the info you provided
good luck