I haven't used Access for more that 5 years and I am rusty. I have inherited a flatfile database with many duplicates. Currently I have about 400 records when I should have only about 140. I want to redesign it. My design problem is that I have husband and wives who may both be members, one a member or neither a member. Each member is given a unique number when they join. The current database lists the woman's first name, last name, address, etc. and then it does the same for the husband, and then it duplicates it creating a third record with the same information for the combined couple so you can send mail to John and Helen Smith, or John Smith and Helen Jones. There might also be grandparents living at the same address creating a 4th record for the same address.l


TABLE ONE
I think I need to create a table with a number (duplicates OK) linking to Table Two, for each person. That record would include data such as membership number (if any), date joined, committee they are on etc.

TABLE TWO would include the address, phone, email, mailing name. and a unique index number that would link this data with table One.

Does this seem that I am on the right track? This is a church database. Right now I have to change 2 or 3 records when someone moves or changes phone numbers.

Why are duplicates ok in table one - surely the membership number will be unique?

I'm assuming in your model that one person only has one registered email address, phone number and address? If so - why are you separating it from the people table?

Table Two has the unique number.
Table One has the linking number that can have duplicates. The husband's record would have the same number as the wife's and both would link to Table 2, the address list.
Not everyone in the database has a membership number.

I'd just go for the following tables:

Option fields are marked with a *. I left the phone number in the User table incase you store mobile numbers - if this isn't a problem then just move the phone field into the Addresses table.

If people can be on multiple committees then the above works fine but if members are limited to one committee each then you could just have a field called committee in the Users table and forget the CommitteePeople table. I put role into the CommitteePeople table just in case you wanted to know who ran the committee etc.

just my 2c

Mike

Mike,

Thanks this looks great, it's so clean and simple. I never thought of using more than two tables.

Hannah