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

[DB88]

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

[ItsMe]

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.

[DB88]

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.

[ItsMe]

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.

[DB88]

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?

[June7]

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.

[DB88]

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

[DB88]

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.

[ItsMe]

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.