I have another question, if you don't mind

I'm working on re-doing the membership database for a local club of 700+ members (the old/existing one is in M$ Works). One of the things we've never been able to track very effectively is past membership. Currently we have people check on the application when they purchase a membership whether it is 'New' or a 'Renewal' - but that doesn't tell me if an individual has say, three years consecutive membership or if he skipped a year in there along the way. I need to be able to track this due to some changes in the club by-laws involving election of officers, etc.

At the moment we just record 'Member Since', 'Date Renewed', 'Date Expires', and whether the member is a Life Member (i.e. no expiration date). Any suggestions on how to lay things out to be able to take these issues into account would be very welcome!

TIA,

Monte

So your members will have between one and many membership records? And each instance of membership will have certain attributes (many of which you have mentioned)?

pootle flump ur codings are working excelent.

Ah... I don't think so. Each member should have exactly *one* membership record, which would have a 'MemberID' field as the primary key. That would in turn be used as a foreign key in other tables to tie various things (mailing address, phone numbers, email addresses, membership sales, volunteer time, etc. back to that particular membership record.

I think

Let's clarify what we mean by membership. A member will only hold one single membership at a time, however if we define membership as a combination of people, terms and time periods then each member may have had many different terms and periods of membership. This is where I was heading. You could term it MembershipHistory or similar, except that many of the memberships would be current.

pootle flump ur codings are working excelent.

Well, when you put it that way, I think we are at least heading in the same direction

Based on some input from others, I am tentatively working with the idea of using something along the lines of one table for sales (tblMembershipSales) which would contain its primary key (SaleID), a foreign key from the main member info table (tblMembers) to identify specifically which member purchased something, a foreign key from another table (tblSellerName) of the four different places in town (currently) that sell memberships for us, another foreign key to a table of the various types of membership sales (Regular, Life, Junior, Extra Key for the Gate, etc.), what they cost, and when the prices are good for, and finally a date for the event.

Just because I'm not entirely sure that came across coherently I attached a PDF of the ER diagram spit out by Access 2007.

The idea (got it from looking at a sample database by another person) is/was to be able to look up in tblMemberSales to determine a) the first time a member purchased a membership, and so be able to track what we currently refer to as 'Member Since', b) check and see if a member really did have a membership last year and really is a 'renewal' or not, c) count and see whether the member had been paid up for either the last year (and hence has voting rights) or the last three years (eligible for nomination from the floor for a officer/board position), and d) check to see if a member is a Life Member (in which case they are by default 'active' for the last however many years since they purchased it). Lastly, e) it should make it easier to sift through all the transactions and see who is buying extra keys - some folks just forget and lose theirs and buy another, or perhaps for their spouse, but others buy four or five... which more likely go to their buddies who are too cheap to buy a membership.

Does what I describe above sound reasonable? Is there a simpler or cleaner way of doing it? Right now I'm finishing up the table relationships and will hopefully soon start learning how to do the joins and queries for the forms and reports that I want. But I'd like to have the tables reasonably straightened out before I get too much further along.

Thanks,

Monte