Results 1 to 2 of 2
2006-12-15, 04:54 #1
- Join Date
- Dec 2001
- Albuquerque, New Mexico, USA
- Thanked 0 Times in 0 Posts
Relationships change after save? (2002)
The following is an oversimplified example of what's happening with an actual database:
Two tables, tblPerson and tblMarriage, but tblPerson is used for several lookups (self-joins); e.g., father, mother are pointers to other records in tblPerson. The relationships were set up as shown in the first image of the attached .pdf file. I saved the layout and closed the relationships window, then reopened it. The joins have now changed, leaving the last instance of tblPerson orphaned, and an unintended loop has been created that nonsensically seems to insist that a person is married to her (his?) father... ????
Can someone please explain what I'm missing here?
P.S. tblMarriage is a separate entity (it can have other attributes besides date), so it's not as simple as replacing that table with another self-join (e.g., adding a "spouse" pointer in tblPerson)
2006-12-15, 09:02 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Relationships change after save? (2002)
There is nothing wrong here, and no incestuous relationships are implied.
It is important to keep in mind that the copies of tblPerson in the Relationships window are just aliases, there is just one tblPerson. The aliases are necessary to indicate different relationships, for if you draw two lines joining a single instance of tblPerson to tblMarriage, that would indicate a relationship involving two fields. When you close the relationship window, Access does not actually store the complete layout. It stores the relationships and it stores the position of the tables/aliases. When you reopen the Relationships window, Access reconstructs the lines from the definition of the tables, and the algorithm it uses tries to use as few aliases as possible. Hence the lines aren't reconstructed exactly the way you drew them. I admit it looks confusing, but as you start filling the tables, you will find that you can set up perfectly "normal" parental and spousal relationships.