Membership & attendance db - table structure?

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 am building the new db in Access 2003.

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


Thanks!

re: Membership & attendance db - table structure?

Suggest you have 3 tables
TblMembers with key of MemberID and things like Name Address, Phone
TblPeriods with a key of PeriodID, PeriodName FromDate and ToDate
JnTblMemberPeriod with a combined key of MemberID and PeriodID and a field
for Present,Absent or Excused (Default being absent)
Depending how you want to input your data, you would have a main form with a
combo box to select the current period and a subform based a query with the
JnTblMemberPeriod and TblMembers for the members showing their names and you
would then input their attendance details
Easy to do reports on any series of periods you want as you have the date
range. You can delete the records from the JnTblMemberPeriod whenever you
fancy

Phil

<crier_67@yahoo.com> wrote in message
news:1129748680.100061.232340@z14g2000cwz.googlegr oups.com...[color=blue]
> 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 am building the new db in Access 2003.
>
> I have searched through a lot of posts on attendance databases but
> still need some help. So I appreciate your input.
>
>
> Thanks!
>[/color]

re: Membership & attendance db - table structure?

crier_67@yahoo.com wrote:[color=blue]
> 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 am building the new db in Access 2003.
>
> I have searched through a lot of posts on attendance databases but
> still need some help. So I appreciate your input.
>
>
> Thanks!
>[/color]

Your second idea is a more appropriate relational design. Based on what
you've said I would dump the 56 field table and create

tblMembers
MemberID
Name
etc.

tblAttendance
PK
fkMemberID
WeekID
AttendanceValue

HTH
--
Smartin