Design to track attendance in multiple series

I am trying to develop a database to track attendance at educational series.
(Among other things, but I'm fine with the way the other functions are coming
along so far.)

Each series has multiple topic tracks, which the participants will remain in
for the duration of that series. Each series is held over 5-6 dates. The
topic tracks are limited to about 4, 2 or 3 of which are usually offered
within any one series.

Participants may attend several different series throughout the year.

I already have tables with potential participants, since they have to
already be registered with our program to attend. Not everyone in those
tables will choose to attend the educational series at any point. However,
further complicating the matter, the participants may come from the table of
current clients or a one-to-many related table of clients' family members. I
can do a union query to combine clients and family members into one list, but
I'm not sure how to preserve their unique identifiers since both tables use
autonumbers for the primary key. Fortunately, I can change how I handle the
primary keys because those tables are in development now, as well.

So, I need to make it easy for the user to

1. Add new series

2. Assign topic tracks to each series

3. Enter the session dates for each series

4. Register participants for each series from the existing tables

5. Enter how many children each participant will bring with them

6. Assign participants to a topic track within each series

7. Track the participants' attendance at each session date within the series

8. Determine which participants attended a minimum number of sessions within
each series

I've put together some moderately complex databases before, but my skills
are rusty and the number of many-to-many relationships I'm dealing with has
me confused as to the most efficient way to design this.

I think that an outline of necessary tables and their relationships will be
sufficient to get me headed in the right direction. Advice on handling the
participant list coming from two different tables would also be greatly
appreciated.

Thanks in advance for your help!

As you realize, the crucial thing is to pin down the one-to-many
relationships correctly. We can suggest some questions to help you do that.

Firstly, are the 'series' you talk of merely an ad hoc collection of topics?
Or is a series likely to be repeated later? Often in education, you have a
series of lectures such as Psychology 101, and essentially the same series
is repeated year after year. If you have this situation, people need to be
enrolled in an instance of the series (e.g. the series that started in
August 2008), rather than the series itself. That is, there is a one-to-many
relationship between the series definition table, and the multiple instances
of the series in a related table.

Next, is a 'topic' merely a sub-group of one series? Or could a topic be
relevant to multiple series? For example, if a topic is 'mental health', the
topic could be part of the Psych 101 series, but it might also be part of
the Well Being 101 series. The answer to this question will determine how
you relate the series and topics tables:
- either one-to-many (one series contains many topics), or
- many-to-many (junction table between series and topic.)

Presumably all lectures in a series are part of a topic. Therefore, it would
seem to me that the dates relate to the topic rather than the series. A
particular topic is offered on a set of dates, so: one topic has many dates.
I would suggest a primary key consisting of TopicID + TopicDate (assuming
you don't have multiple streams of the topic on the same date.)

Now for the people themselves. You will want everyone (adults, children,
whoever will atend) in the one table. You can then create a one-to-many
relationship between the instance of the series and your Clients table, so
that many people can enrol in an instance of a topic.

Now your attendance table will contain:
- TopicID
- TopicDate
- CleintID
- AttendCodeID
The first 2 fields in combination relate to your TopicDate table. The third
relates to your client table. The last one relates to a little lookup table
that has fields for whether they attended, were absent, were late, were
excused, or whatever.

You mentioned enrolling people as families? I'm not sure that's adequate for
tracking attendance, as it may be that some members attend on a particular
date, but others can't be there. Therefore I suggest you enrol individuals
rather than families.

You could track which persons are in each family, and even design an
interface so that the user can select a family and it puts all the
individuals into the series instance. But this is about the interface rather
than the table design.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"J. Renee" wrote in message
...
I am trying to develop a database to track attendance at educational
series.
(Among other things, but I'm fine with the way the other functions are
coming
along so far.)

Each series has multiple topic tracks, which the participants will remain
in
for the duration of that series. Each series is held over 5-6 dates. The
topic tracks are limited to about 4, 2 or 3 of which are usually offered
within any one series.

Participants may attend several different series throughout the year.

I already have tables with potential participants, since they have to
already be registered with our program to attend. Not everyone in those
tables will choose to attend the educational series at any point.
However,
further complicating the matter, the participants may come from the table
of
current clients or a one-to-many related table of clients' family members.
I
can do a union query to combine clients and family members into one list,
but
I'm not sure how to preserve their unique identifiers since both tables
use
autonumbers for the primary key. Fortunately, I can change how I handle
the
primary keys because those tables are in development now, as well.

So, I need to make it easy for the user to

1. Add new series

2. Assign topic tracks to each series

3. Enter the session dates for each series

4. Register participants for each series from the existing tables

5. Enter how many children each participant will bring with them

6. Assign participants to a topic track within each series

7. Track the participants' attendance at each session date within the
series

8. Determine which participants attended a minimum number of sessions
within
each series

I've put together some moderately complex databases before, but my skills
are rusty and the number of many-to-many relationships I'm dealing with
has
me confused as to the most efficient way to design this.

I think that an outline of necessary tables and their relationships will
be
sufficient to get me headed in the right direction. Advice on handling
the
participant list coming from two different tables would also be greatly
appreciated.

Thanks in advance for your help!