Several records for the same person with different membership numb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have for example a person that belongs to the same organization
but is a member in 3 or 4 different clubs. The database has lets say 4
records for the same name. I need to some how get them all together under on
name but with all the different membership numbers. So that if this persons
lets say dies I can update on record that will update all four records. Any
clues would help?
 
Dear Keith:

The proper design is to have tables that do not duplicate information about
a person. Information such as when the person was born or dies should be in
a single table. Information about any person's memberships in various clubs
should be in one or more additional tables. If possible, keep membership
information all in one table. This might include the date the person
joined, the date they left, as well as a key to each club they have joined.
It is strongly recommended not to propagate information like the person's
address, date of birth, date of death, etc. into multiple tables.

Updating the same information into multiple tables, or multiple records in
one table, is strongly discouraged. Use better table design instead.

Tom Ellison
 
I currently have for example a person that belongs to the same organization
but is a member in 3 or 4 different clubs. The database has lets say 4
records for the same name. I need to some how get them all together under on
name but with all the different membership numbers. So that if this persons
lets say dies I can update on record that will update all four records. Any
clues would help?
Click to expand...

Well, you need to start using Access as it was designed - as a
relational database.

You should have several tables - at least three. Each table should
contain information about one type of entity; you'll have one table
for People (with one and only one record per person, and no membership
or organization information); a second table for Organizations or
Clubs, with no person or membership information; and a third table
linking them, indicating who is a member of what. For example:

People
PersonID Autonumber Primary Key
LastName
FirstName
<other bio data as needed>

Clubs
ClubID Autonumber Primary Key
ClubName Text
<other fields about the club as an entity>

Membership
PersonID Long Integer (link to People)
ClubID Long Integer (link to Clubs)
<any fields needed to describe this person's involvement in this
club, e.g. date joined, membership number, role, etc.>


John W. Vinson[MVP]
 
Ok, just to be completely clear. Let's say that I belong to 3 clubs.

Club 607 Membership # 607-629
Club 109 Memberhsip # 109-219
Club 999 Membership # 999-413

How do I link all 3 of these to one person? I understand about having the
correct tables. I had already started to split the information into 3 tables.

In this situation, If I died, how do I make one update and show all 3 of
these memberships that I was dead? Without having to make 3 entries?

Keith
 
Dear Keith:

For reference, I will call your tables "Club" "Member" and "ClubMember"

This accruately reflects how it should be built.

The Club table could have a column "ClubNo" in which you put your sample
values 607, 109, and 999 for the data shown.

The Member table would similarly have a MemNo column in which you put the
sample value 607-629, 109-219, and 999-413.

In the "ClubMember" table you should have both the above columns, both
ClubNo and MemNo. This creates the potential "cross product" of all values
from Club and Member. If you have 9 clubs and 20 members, you have the
maximum potential of having 180 ClubMember combinations. I am assuming you
do not permit a member to join any club more than once.

Keep the "DateOfDeath" column in the Member table. Do not put this in the
"Club" table. In a query, you can JOIN to obtain this whenever needed.

This allows you to store DateOfDeath only once per member, which makes sense
if they can only die once. The same DateOfDeath value applies to a member
with respect to any and all the clubs he has joined. Storing an atom of
data more than once only makes the job of maintaining a database much more
difficult in the long run.

Tom Ellison
 
Back
Top