Table Design

[booismycat]

I'm stuck with a table design and hoping I can get a little guidance.

My data consists of peoples first/last names along with their spouse or partner's first/last names. Partner's last name may or may not be the same as the other person's last name.

Ex:
Roy Rogers & Dale Evans.
Mickey Mouse & Minnie Mouse
Popeye the Sailorman (no spouse or significant)

After each name is paired with spouse or significant other (if available), I need to pair them with at least one and maybe two addresses.
So, this would require a one-to-many table relationship.

Doesn't sound difficult.


But, here is where I stumble:

I created a NAMES table that contains one name per record.
I created an ADDRESS table that also contains one address per record.
I created a JOIN table so that each name record can be associated with one or more addresses.

This works - up to this point.

My problem - How do I identify Roy Rogers is with Dale Evans or Mickey Mouse is with Minnie Mouse?
I need to be able to select either name and link them to other tables further into this project so combining the names as one record doesn't seem to be the right solution.

If Roy and Dale have two addresses, I want a query that lists TWO records - not four. With no link to Roy and Dale, a simple query of FirstName/LastName/Address will produce two records for every name linked by the join table. Two names = 4 records.

What am I missing?

[June7]

So maybe have a SigOther field in NAMES. Insert the record ID of person who is the partner. To get the SigOther info in query NAMES table will self-join. This is a recursive data structure.

Otherwise have an AccountID or FamilyID field in NAMES.

[booismycat]

I can insert the recID into a SigOther or FamilyID field using vba but at the moment have no clue how I would pull related records via a query using your method.

If I have
Record #1 with RecID = 1 and Name is Roy Rogers and
Record #2 with RecID = 2 and Name is Dale Evans

Using a form and vba, I can insert 1 into SigOther for Record #2 and/or 2 into SigOther for Record #1...

But, I see no way to query these two records based on what is in SigOther to correctly match the two names.



Can you clarify?

[June7]

You can't because there is no data to indicate what records are related. This is the data you need to create. You will have to do data entry to add the new data. This will be a long and tedious effort.

[ArviLaanemets]

So you need 2 additional tables. In one you define e.g. family, in other you attach people to family.