Help with setting up a Many to Many relationship
I'm looking for some ideas on how to set up relationships so that the information needed is easily accessible. I have a table, called Vehicle, that contains a record for each vehicle. There is also a table, called Comms, that lists all communication systems. One vehicle can have multiple communication systems. Thus I connect the two tables using a line item table, LI_VehicleComm.
I want to be able to look at one vehicle record and see all other vehicles that it can talk to. Not all communication systems are compatible. I have a table, called Compatible, that is just a list of each combination of communication systems that can work together. I.e. Comm1 can talk to Comm1 (one record) and Comm1 can talk to Comm2 (another record).
Is it possible to set up the relationship so that when looking at one record in Vehicle I can view all other vehicles that it can communicate with?
My initial set up idea was Vehicle TO LI_VehicleComm TO Comms TO Compatible TO LI_VehicleComm(duplicate) TO Vehicle(duplicate). While that does allow me to drill to the needed information, it isn't optimal. I can't view the needed information on one screen. Plus it is kind of convoluted.