Expert: Scottgem - 1/1/2006

Question
Hi, I'm using Access 2003 and have created a relatively simple database recording and handling customer details in my health club.  When a member enters the gym we locate their member number on the 'Member entry form' by scrolling thru all active members using the mouse wheel.  When their form is reached you simply check their membership is payed-up and click on a 'Enter visit date' button which automatically changes their last visit date to today's date.  The idea is that I have the last visit date of all members recorded in the database.  With this info I can see when they last used the gym and if this date is more than, say, 2 months ago they are considered a lapsed, or expired, membership.  It works very well, however, my problem is this: I would like the 'Enter visit date' button to add the current date to their previous dates in a seperate member linked sub-form, thus recording all their visit dates and not just the last.  The current set up simply adds the latest date to a field on their table and changes it each time the button is clicked.  I'd like it to record all dates so I can see all the visit dates for a particular customer and run queries on total numbers of visits per month etc.  Can you help me?
Thank you
Dave

Answer
As I started to read your question, the thing that came to mind was you should be using a visit table rather then a last visit date. And then I got to the point where that's what you wanted to do.

You should have at least 3 tables here (probably more). You should have:

tblMember
MemberID (Primary Key Autonumber)
FirstName
LastName
DOB
other info about each member.

tblMembership
MembershipID (PK Autonumber)
MemberID (Foreign Key)
MembershipType (FK to membership type lookup table)
AmountPaid
EffectiveDate

tblVisits
VisitID (PK Autonumber)
MemberID (Foreign Key)
VisitDate

What this gives you is a way to record when each member has paid for membership and what type of membership. And each visit.

I would then have a form bound to tblMembers to show the member's personal data and two subforms, one to show the membership info, the other for the visit info. Both would be linked to the main form on MemberID. For the Visit subform, I would use the GotFocus event of the date control to run this line of code:

Me.txtVisitDate = Date()  (use Now() if you want to record time as well)

As soon as you click in the control the current date (and time) will be filled in.

You might want to do some research on database normalization. This is an essential design technique. The basis of normalization is to reduce or eliminate redundant data. The principle of relation database is that data should exist in only ONE place. That you use keys to join tables to that data. As an example, you might have address and phone info in tblMembers. But what if you have multiple members from the same household. In that case, you might want to have a separate address table where the addresses are listed only once and then link to the address using its key field in tblMembers.

HTH
Scott<>