Husband and Wife (Mr. & Mrs.) Relationships 1

There are any number of approaches to the database design that might work for you. If you are not familiar with database design, it will benefit you to read some articles on database deign concepts. Here are some recommendations.

Download "Understanding Relational Database Design" from Microsoft. It is available for all versions of Access from 95 to 2002. This URL points top the Access 2000 download page.

Read "Creating and Working With Databases" and "Creating and Designing Tables" in Access 2000 help. I can't remember if those subjects were available in Access 97.

You may also benefit from reading "Fundamentals of Relational Database Design" at
----------------------------------

I share a couple of quick ideas on your database.

Each individual voter is an entity and should be represented by a row in a table. Therefore the main attributes of a voter should be kept in the same table for all voters. This table should have an "Identification Number" as the primary key and a list of attributes.

One of the attributes for voters could be "spouse." The spouse column could hold the spouse "Identification Number" if the spouse is in the table or zero if not.

With both husband and wife in the same table a self-join would be required to get all the info required for your mailing labels. Take a look at the example that follows.

Select a.FName, a.LName, b.FName, b.LName, a.Address, a.City, a.State, a.ZipCode
From Voters a left join Voters b
On a.VoterID=b.SpouseID

This looks good to a point but if both husband and wife have a SpouseID pointing to the other person, the query will return two records for each couple. Should both husband and wife carry the SpouseID? If only one carries the ID then the query would only return one record per couple. Are there other ways around this problem? Yes, but I’ll leave that for you.

Hopefully, this gives you some ideas to get started. It is brief and very sketchy. That's why I recommend the readings above. Also, examine some of the sample databases available with Access to get more ideas.
Terry

X-) "I don't have a solution, but I admire your problem."

There are several ways how to solve your problem.
1. You can create separate table for men and related table (one to one) for his wives. Select join type of relation between these tables "All records of table 'Men' ...". Then you will be analise married status of men with existence of record in the related table for each man.

2. More difficult, but at the same time more comprehensive way: you can create field in the table which include data of all people (men and women) for married person identification. In this field you can write person ID from same table.

Aivars