Results 1 to 8 of 8
  1. #1
    mkirby7575 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3

    Disappearing Table Relationships

    I have foreign key tables within my relationships that are shown three times (3 instances, same table),since there are three fields in the primary key table that each relate to the same table.



    For instance in the primary table there are fields for PUCarrier, LHCarrier, and DVCarrier all of which relate to a table called tblCarriers. I added multiple instances of the tblCarriers table and linked each of the three fields in the primary table to these instances. Referential integrity was not allowed and therefore Attachment 39698was not checked. However when I close the database and reopen all of the relationships from the three fields in the primary table are shown as relating to the first instance of tblCarriers.

    Any idea why or what I can do to fix this?
    Attached Thumbnails Attached Thumbnails IMG_3939.JPG  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I probably wouldn't bother with setting relationships. Since can't enforce referential integrity, only purpose setting relationships serves is to automatically set links in queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mkirby7575 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3
    So is there a problem with multiple instances of a table within the relationships window?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Actually should be possible. And I just tested with my db. The Relationshp settings hold.

    But don't think I've never seen each of the duplicate tables have a second table linked to each. If you want to provide db for analysis, follow instructions at bottom of my post.
    Last edited by June7; 09-11-2019 at 04:19 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suspect it is just a presentational issue. you can look in the msysRelationships table to see the relationships you have created. From what you are saying the image you are showing is the 'before' image when you create the relationships which displays as I would expect. Suggest if you want to continue investigating, show the 'after' image.

  6. #6
    mkirby7575 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    3
    Quote Originally Posted by Ajax View Post
    suspect it is just a presentational issue. you can look in the msysRelationships table to see the relationships you have created. From what you are saying the image you are showing is the 'before' image when you create the relationships which displays as I would expect. Suggest if you want to continue investigating, show the 'after' image.
    Thank you for your note. As you mentioned here is the view of the relationships after closing and reopening the database. You will note that the links between tblCarriers[CarrierID] and each of the sub tables have been rerouted to the first instance of the subtable [tblCarrierContacts]

    Click image for larger version. 

Name:	RelationshipsAfter.JPG 
Views:	15 
Size:	101.1 KB 
ID:	39705

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I misunderstood. I thought the duplicate tblCarrier links to tblShipments fields were failing. The duplicate tblCarrierContacts are not needed because there really is only one link between tblCarrierContacts and tblCarriers. Not multiple fields as in tblShipments. Even though you repeat tblCarriers, it's still the same two fields linked.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    strange - I responded to this at the time, saying much the same thing - but my post seems to have disappeared.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-07-2017, 01:10 AM
  2. Replies: 8
    Last Post: 05-12-2017, 01:10 PM
  3. Disappearing Act
    By CementCarver in forum Forms
    Replies: 4
    Last Post: 09-04-2013, 03:08 PM
  4. Table Relationships help
    By rzw0wr in forum Database Design
    Replies: 3
    Last Post: 06-21-2013, 07:42 PM
  5. Table Relationships
    By jp2access in forum Database Design
    Replies: 3
    Last Post: 06-19-2009, 10:20 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums