table design: members/committees

[KRosier]

I constructed a database nearly ten years ago with very little knowledge
on my part. I’m self-taught and have learned by example. I’m sure
there is a better way to set this up, but I keep coming up against a
mental block, so that, even though the old way may be clunky and not
proper, it works, so I don’t mess with it. I’m finally getting around
to correcting it and need help.

MS Access 2000

one member can be on many committees
many members can be on one committee

My database is set up so that there is a separate field for each
committee (15+) in the member table. In this manner, if a new committee
is added, I must add a new field and update any queries/forms/records
associated with it.

The member table contains (simplified):
name
fields with other individual information
committee1 (yes/no type)
committee2 (yes/no type)
committee3 (yes/no type)
etc.

Should there be a separate table for the committees? If so, what would
the relationship between the two tables look like? Should the member’s
committee information all be stored in one field in the member table?
Any help would be very much appreciated.

Kathy

 

[Duane Hookom]

Using committee names as field names is not a good idea. Consider reading up
on normalization. Jeff Conrad has some good links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

John Viescas' latest book has a complete application for you ready to go on
CD. The book also supports the app and provides a lot of other good
information. www.viescas.com