Relate rows in a single table

[HPG]

I am in the following situation: I have made a table containing information about people I know. I am trying to find a way to keep track of marriages. Marriages are relations between people in the same table. So far I have made a table called marriages. This table contains two columns (and a key). These two columns each have a relation to the same column in the other table. PersonID is foreign key in man, and also in whife. I invented this way myself, and I am uncertain about it. But in my mind it should work fine. The problem I have is that I am unable to get the information back out. Do I use SELECT? Or does this way work at all?

[orange]

I'd suggest searching for info on these topics, just to get a view of what's involved

Representing Hierarchies in database tables
Genealogy database structure
Modeling hierarchical data
gedcom

This is not a trivial subject. I'd suggest getting a look at a number of options.

[Amicron]

If your primary table looks like this:

PersonT
PersonID
FirstName
LastName
(etc.)

I would create a second table to hold the marriages, as you've done:

MarriageT
MarriageID
Spouse1
Spouse2

Spouse1 and 2 are FOREIGN KEY fields pointing to a PersonID.

Now your data would look like this:

PersonT:
1, Joe, Smith
2, Bill, Jones
3, Amy, Smith
4, Sue, Jones

MarriageT:
1, 1, 3
2, 2, 4

You can now easily throw these two tables together in a query. Just add MarriageT once and TWO copies of PersonT. Relate together the Spouse fields to the proper IDs, and you're all set.

Richard Rost
AccessLearningZone.com

[orange]

Richard,
In MarriageT, what is spouse2?
And how does it realte to this

MarriageT:
1, 1, 3
2, 2, 4

Just trying to clarify...

[Amicron]

Spouse1 and Spouse2 are the two people who are married. They are foreign keys that relate back to a PersonID. The values in the MarriageT table demonstrate the relationships:

1,1,3 = ID1, Person #1 is married to Person #3
2,2,4 = ID2, Person #2 is married to Person #4

[orange]

Ok, I saw that. I thought you were trying to show more.

Let's say any given spouse gets married multiple times.
The MarriageT would require more fields -- a la Ancestry etc --
DateOfMarriage, LocationOfMarriage....

I wasn't seeing how the table could handle anything more than a single " 2 people got married", and I see that's what it was meant to be. However, the poster was looking for a database

to keep track of marriages.

and I think the MarriageT is a great start, and now the poster must determine what it is about a marriage that should be recorded/managed.

Thanks.

[Amicron]

Oh, absolutely... this was just to give a basic illustration of how to set up the relationship. You'd want other fields in there to keep track of the details, of course. Perhaps even an IsActive value to recognize if they're still married... or maybe a DivorceDate.