*Novice* Basic Table & database structure Question

[bernmc]

Hello all

Firstly, please note I'm an access virgin . I did play with visual dBase a long time ago....

I'm setting up a database for my wife's amateur dramatics company, and am struggling to decide how to arrange the tables and their relationships.

This is how the company works. It has several members. They are charged for each Friday teaching session they attend. For each date, a member may attend - and be charged; be absent without notice - and be charged; or be absent with notice - and not be charged. The register needs to indicate which of these three apply to each lesson date, and I'll calculate monthly fees from the info.

So I have a member details table. Simple enough. It's what to do next... Would I set up another table where we can enter the dates of the lessons -there would then be a 1-many relationship for each member and the lesson dates. However, all dates apply to all members... so is this the way to do it? I'm not sure how to incorporate the present/absent with notice etc to this structure though. Hope I'm being clear...

On paper it would look like this:

Joe blogs:
10 Jan - present
20 Jan - present
30 Jan - absent with notice

Mary Crun
10 Jan - present
20 Jan - absent without notice
30 Jan - present

Conan the Librarian
10 Jan - etc. etc. etc.

I would then do a query, count the present & absent without notice for Joe blogs, and multiply by the lesson fee... and so on.

Any pointers/advice would be much appreciated, or if it's been answered before, a pointer to the info... ta!

Bernard

 

[The_Doc_Man]

Would I set up another table where we can enter the dates of the lessons -there would then be a 1-many relationship for each member and the lesson dates. However, all dates apply to all members... so is this the way to do it?

Yes, almost.

Have a table of persons with an ID code for each. Include a join date and a quit date. (If folks join and leave frequently, you might need to split this into two tables.) Suppose this is a contract situation where your members join by contract with a specific period of membership. When a person is a member, their join date is in the past and their quit date is in the future as determined by the contract. (If it is more wide-open, pick a date so far in the future that you'll all be dead and buried.) With each new contract, you enter a new person record.

You can also hold a record of lesson dates in case there is something you wanted to track about the lessons. Say, lesson on 4-Oct-2004, subject was "Method Acting", instructor was "Stan I. Slavski." Just for giggles, add a yes/no flag that says "lesson grid populated." You can pick a shorter name if you like.

Now design & create an empty table that is the junction of these two.

tblLessonPerson
long (foreign key) fldPersonID - who took the lesson
date - fldLessonDate - when it occurred
integer - fldLessonCode - present, absent, excused
yes/no - fldPaid
yes/no - fldNewEntry
currency - fldFee
etc. etc. details about payments for lessons.

Now, every time you have a lesson, use a form to create that lesson. By default, your "populated" flag should be false.

The trickiest part is to populate the junction table. Once you build the table, you can populate it with an insert query that adds a record for every person who was a member at the time (I.e. join date is in the past, quit date is in the future.) The records show NO for paid and YES for new entry. The code shows "present" for everyone.

OK, now you need a form that shows you records that are not yet paid OR are new entries (which still won't be paid...). Your form should automatically clear the NewEntry flag for that record after the first time you touch it. You have the chance to set the code to absent or excused.

 

[bernmc]

Thanks Pat & Doc... I'll give it a go - I'm sure I'll be back for more