membership & attendance table structure

[crier_67]

Hello all,

I'm relatively new to Access and could use some direction on this
task. I have a group with a FoxPro 2.6 for DOS membership & attendance
database with about 200 member records. The group operates on a 28
week schedule, with meetings once each week. At the end of 6 months
they create a new 28 week schedule, but preserve the prior 6 month
attendance values. They view & edit attendance records for the
"current period" and the "previous period". They do not need
to retain records prior to the previous 6 month period, nor do they
require the records to be deleted from the database. They do not
always have entries for all 28 weeks; they just begin their new 28 week
period between June/July and December/January. They might only have
entries up through week 20.

In the current FoxPro database, there are 56 fields in the membership
table labeled A1 - A28 and B1 - B28 which correspond with weeks
1-28 for each period. These fields hold the values of Present, Absent,
or Excused. There is a function in the database to reinitialize a new
28 week schedule but I am not clear on how it works. Reinitializing
clears the form that allows the user to enter the dates for weeks 1-28,
but I do not know how it 'moves' between the A & B fields. (so
that the "current period" is B1-B28 instead of A1-A28)

I originally thought I'd create tables MEMBERS, CURRENT_PERIOD,
PREVIOUS_PERIOD, and then at the end of each 28 weeks I'd use a query
to delete the records from PREVIOUS_PERIOD, copy records from
CURRENT_PERIOD to PREVIOUS_PERIOD, then delete the records in
CURRENT_PERIOD. The more I think about it, the more I think I'm on
the wrong track with this. Should I keep the extra 56 fields relating
to attendance in the membership table? Or should I break the
attendance data out into a new table and use an ID # to create a
relationship between the member & the member's attendance? Is there
another solution I'm not thinking of?

(Hope all that made sense.)

I have searched through a lot of posts on attendance databases but
still need some help. So I appreciate your input.

Thanks!

 

[crier_67]

Oops, I meant to include that I am creating the new db in Access 2003.

 

[John Vinson]

Hello all,

I'm relatively new to Access and could use some direction on this
task. I have a group with a FoxPro 2.6 for DOS membership & attendance
database with about 200 member records. The group operates on a 28
week schedule, with meetings once each week. At the end of 6 months
they create a new 28 week schedule, but preserve the prior 6 month
attendance values. They view & edit attendance records for the
"current period" and the "previous period". They do not need
to retain records prior to the previous 6 month period, nor do they
require the records to be deleted from the database. They do not
always have entries for all 28 weeks; they just begin their new 28 week
period between June/July and December/January. They might only have
entries up through week 20.

Ok... sounds reasonable.

In the current FoxPro database, there are 56 fields in the membership
table labeled A1 - A28 and B1 - B28 which correspond with weeks
1-28 for each period.

But that doesn't. "Fields are expensive, records are cheap". You have
a Many (members) to Many (meetings) relationship; a better design
would be three tables, one of Meetings (with a Date/Time field
indicating the date of the meeting, and any specific information about
that meeting - speakers, special events, whatever); a table of
Members, with a MemberID and the member's bio and contact data, and a
table of Attendance, with the meetingID, personID, and Status
(present, absent, excused).

With this design, there's no need for periodic updates; if you want to
see who attended in a particular date range, just run a query on that
date range.

Your intuition was quite correct. You do need to break this out into a
related table!

John W. Vinson[MVP]