Microsoft Access Database Design

[ixeye]

I am creating a database for church and you would not think that
what I am doing is reinventing the wheel but gosh if it feels
like it The database is being created to track who is coming
to our church and when so I originally created two tables: one
for all the info on a particular someone and two for the dates.
We don't have a unique ID number that we can use for someone
specific to link the two tables so I finally chose to create one
big table with all the personal info and with all the dates
that they attended as Yes/No fields. 2 problems arose from doing
it this way. The number of fields is huge if I talk about one
field per date and then making an automated report is almost
impossible since parameter queries I believe are not created
for variable fields only variables in each field??? Any ideas,
anybody on the best way to design this database?

 

[Joe Fallon]

My Church program has 22 tables in it.

I suggest you find someone who is an Access expert and can help you build it
correctly.

 

[Sandra Daigle]

You are in for *major* headaches if you try to do it this way! You don't
need a unique ID number for each person but you do need a way to uniquely
identify them - otherwise how will your user(s) select the correct person to
enter attendance information? IOW, you really have to come up with some
combination of fields that constitutes a unique key - whether you use that
as the primary key for the table is up to you. I personally prefer to create
an autonum field which serves as a surrogate key, then create a unique index
on the field(s) that make up the unique key for the table.

Now, regarding the attendance information, you really need to put this into
a separate table - PersonAttendance. This way you merely add a new record
for each date the person attends a church function. Otherwise, you have to
make table, form and report changes to accomodate every new date. Also, you
will eventually hit the limit on # fields per table and as you've already
discovered, reporting is difficult with this highly unnormalized design.

 

[ixeye]

Would you consider sending me your Church MDB file (without personal
info of course) so I could look at how you have set up the fields and
relationships?
Brent Carey
(e-mail address removed)

You are in for *major* headaches if you try to do it this way! You don't
need a unique ID number for each person but you do need a way to uniquely
identify them - otherwise how will your user(s) select the correct person to
enter attendance information? IOW, you really have to come up with some
combination of fields that constitutes a unique key - whether you use that
as the primary key for the table is up to you. I personally prefer to create
an autonum field which serves as a surrogate key, then create a unique index
on the field(s) that make up the unique key for the table.

Now, regarding the attendance information, you really need to put this into
a separate table - PersonAttendance. This way you merely add a new record
for each date the person attends a church function. Otherwise, you have to
make table, form and report changes to accomodate every new date. Also, you
will eventually hit the limit on # fields per table and as you've already
discovered, reporting is difficult with this highly unnormalized design.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I am creating a database for church and you would not think that
what I am doing is reinventing the wheel but gosh if it feels
like it The database is being created to track who is coming
to our church and when so I originally created two tables: one
for all the info on a particular someone and two for the dates.
We don't have a unique ID number that we can use for someone
specific to link the two tables so I finally chose to create one
big table with all the personal info and with all the dates
that they attended as Yes/No fields. 2 problems arose from doing
it this way. The number of fields is huge if I talk about one
field per date and then making an automated report is almost
impossible since parameter queries I believe are not created
for variable fields only variables in each field??? Any ideas,
anybody on the best way to design this database?

 

[Sandra Daigle]

As soon as I get a chance I'll send you a snapshot of the relationships
diagram - I don't have a current copy at my fingertips right now but I will
get back to you with this as soon as I can.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Would you consider sending me your Church MDB file (without personal
info of course) so I could look at how you have set up the fields and
relationships?
Brent Carey
(e-mail address removed)

You are in for *major* headaches if you try to do it this way! You
don't need a unique ID number for each person but you do need a way
to uniquely identify them - otherwise how will your user(s) select
the correct person to enter attendance information? IOW, you really
have to come up with some combination of fields that constitutes a
unique key - whether you use that as the primary key for the table
is up to you. I personally prefer to create an autonum field which
serves as a surrogate key, then create a unique index on the
field(s) that make up the unique key for the table.

Now, regarding the attendance information, you really need to put
this into a separate table - PersonAttendance. This way you merely
add a new record for each date the person attends a church function.
Otherwise, you have to make table, form and report changes to
accomodate every new date. Also, you will eventually hit the limit
on # fields per table and as you've already discovered, reporting is
difficult with this highly unnormalized design.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I am creating a database for church and you would not think that
what I am doing is reinventing the wheel but gosh if it feels
like it The database is being created to track who is coming
to our church and when so I originally created two tables: one
for all the info on a particular someone and two for the dates.
We don't have a unique ID number that we can use for someone
specific to link the two tables so I finally chose to create one
big table with all the personal info and with all the dates
that they attended as Yes/No fields. 2 problems arose from doing
it this way. The number of fields is huge if I talk about one
field per date and then making an automated report is almost
impossible since parameter queries I believe are not created
for variable fields only variables in each field??? Any ideas,
anybody on the best way to design this database?