Results 1 to 9 of 9
  1. #1
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110

    Need two occurances of same table in relationship graph. How do I fix that?

    Hello,

    I have a many to many relationship between tables Platform and Comms. I accomplish this with a junction table, LI_PlatComm. I want to know, using the communication systems, which platforms can talk to each other. A comm system can talk to itself and some are compatible with other systems. I created the table, Compatible, which is a list of communication systems that can talk. Aka it has field kf_comm (a specific communication system's key) and field kf_compatiblecomm (key to the comm system it can talk to).



    My relationship table looks like Platform TO LI_PlatComm TO Comms. How do I incorporate Compatible? Does Access allow duplicate table occurances in the realtionship graph? If yes, then I could do Platform TO LI_PlatComm To Compatible (using kf_comm) TO LI_PlatComm Duplicate (using kf_compatiblecomm) TO Platform Duplicate. Other database software allows this but when I tried it in Access it didn't appear to be working as desired. Also I've seen no mention of duplicate table occurances in my reference book. I could probably remove the need for Compatible table by creating a multi-value field in Comm table that lists the keys of all compatible communication systems. But I will still need a duplicate of LI_PlatComm.

    Any ideas on how to set up this relationship? Or ideas on another technique to get the data I need?

    Thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Are other developers linking to your tables? If not, forget adding the second relationship in the relationships window and enforce constraints via forms and VBA.

  3. #3
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110
    Sorry, I don't understand what you mean by "enforce constraints via forms and VBA". I'm not trying to restrict anything. I just want to know based on the communication systems on board a platform what other platforms they can talk to.

    I'm the only developer who is creating relationships.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    What are we talking about here? I do not see what relationships have to do with a platform's communication systems. Hardware is hardware and hardware depends on DLL files. If you want to communicate with the hardware then you will need an API that employs an entry point to the hardware's DLL file(s). If you want to communicate with the platform, you will need to use an API.

    If you have tables and you want to query relative information based on their key values, create a JOIN on the Key fields.

  5. #5
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110
    The table is called "Platforms", it is a list of our vehicle types. The table is called "Comms", it is a list of radios, and other communication devises. I could literally change my names to TableA and TableB. I want to build a RELATIONSHIP between tables, this has nothing to do with hardware/software of a computer. I just want to link records. I have no idea what you are talking about.

    As I mentioned above, the relationship I need to view my desired records is:
    Platform TO LI_PlatComm TO Compatible TO LI_PlatComm TO Platform

    However that relationship is invalid unless I can create duplicate table occurances of LI_PlatComm and Platform. I don't believe that Access allows that. So I need a better way to set up the realtionship.

    My current database relationship graph looks like:
    Platform TO LI_PlatComm TO Comm.

    How do I incorporate the table, Compatible so that given one platform I can see a list of all other platforms it can talk to?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Tables can be included in the relationship builder multiple times. They will automatically be named like Table_1, Table_2, etc. However, I am not understanding your data structure but it appears to be a circular reference, review http://www.codeproject.com/Articles/...atabase-Design

    Many experienced developers don't bother setting relationships, especially in databases designed to prevent user interaction with tables and queries. Data integrity is managed through forms and code. This was what I think ItsMe's original comment was regarding.
    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.

  7. #7
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110
    Never mind I figured it out.

    Create a query that finds all Comms for a given platform. Called it ListCommPerPlatform
    Create a query that finds all Platforms using a given Comm. Called it ListPlatformPerComm
    Then connected them both to Compatible table. ListCommPerPlatform TO Compatible TO ListPlatformPerComm

  8. #8
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110
    Yes, tables can be in the relationship builder multiple times. However they are not sperate table occurances. When I build the relationship I described, it was circular because Access does not treat the duplicates as seperate table occurances. My other software did treat them as seperate occurances so there was no circular problems.

    I solved my problem by connecting two querys to the table Compatible. I'm slowly starting to think in Access not FileMaker.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    The relationships window is to help manage constraints like referential integrity.

    Use the query builder to create queries. If you do not like a JOIN that was automatically created by the query builder, delete it. Alternatively, there is a setting in the Relationships Window that you can tick if you do not want the relationship to be automatically represented as a JOIN in the query builder. However, the query builder may create a JOIN automatically when adding a table or query that has similar field names, regardless of the Relationship Manager/Window.

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

Similar Threads

  1. Counting number of occurances by date
    By michaelwh1 in forum Access
    Replies: 2
    Last Post: 09-20-2013, 01:35 PM
  2. Replies: 6
    Last Post: 02-29-2012, 03:13 AM
  3. Only include unique occurances
    By filla_dilla in forum Queries
    Replies: 8
    Last Post: 07-18-2011, 10:22 PM
  4. Replies: 3
    Last Post: 01-10-2011, 10:31 AM
  5. Need Zero Values in Table For Graph
    By TexMax007 in forum Access
    Replies: 2
    Last Post: 06-02-2010, 10:02 AM

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