Results 1 to 2 of 2
  1. #1
    New Lounger
    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)


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts