Table design

[Ellen]

Hello,
I'm designing a database in Access 97 to track attendance
for the year. The table will contain a field for the
members and 52 fields for the each week of the year. From
time to time I'd like to be able to get percentages of
meetings attended by individual participants. I plan for
the week fields to accept a null (meeting hasn't yet
occured), a zero (a missed meeting) or a one (an attended
meeting). From this the percentage of attendance can be
calculated. I assume this design will work.

I have two questions:

1. How do you set the validation criteria to accept only
a null, 0 or 1 in the validation rule box?

2. How would a percentage of attendance be calculated?

Thanks,
Ellen

 

[Immanuel Sibero]

Hi Ellen,

Comments on design:

You're approaching this with a spreadsheet design. I'm not sure if Excel
might be more appropriate for this task. I guess it really depends on how
familiar you are with Access and whether the task at hand is as simple as
what you described below. To do this in Excel would simply be a setup of 53
columns - Participant Name on column A and the 52 weekly meeting starting on
column B.

To do this in Access, you have to *abandon* the spreadsheet design
viewpoint. You would set up tables in the commonly referred to as relational
design. Here's a summary:

- Create 3 tables - tblParticipant, tbl Meeting, and a junction table
tblAttendance.
- There is a many-to-many relationship between tblParticipant and tblMeeting
- tblParticipant will store one record for each participant, tblMeeting will
store one record for each meeting (i.e you would have 52 records in
tblMeeting.
- You wont have to bother with null, 1, 0 to indicate attendance, since a
record in tblAttendance does just that (i.e. signifies a particular
particpant attending a particular meeting).
- the number meetings attended by a participant can be obtained from
tblAttendance.
- the number of missed meetings = number of meetings as of the current
date - number of meetings attended (i.e. above)
- percentage of attendance = number of meetings attended / number of
meetings as of current date

Please post back if you have further questions.

HTH,
Immanuel Sibero