Issue with Relationships (I think) (1 Viewer)

[Solved] Issue with Relationships (I think)

Hi all,

I'm a new user to access and this forum so sorry if I don't understand reply's straight away.

I am looking to do something quite specific and couldn't found any help online and wondering if you guys could help me.

I am building a database to track interfaces between components on a project I am working on, as such I've got a table full of data on the specific components (called component data).

I have made a separate table to track the interfaces (called the interface table), with two columns referencing the unique ID numbers of the two components that are connected together from the component data table.

The problem is when I come to make a form to pull up a specific component I want to be able to see all the connections that that component has, but unfortunately it only shows the connections that I reference first (in the relationships tab). Please see the pictures as I haven't explained that too well. I want to be able to see connection 4 to 3 on component 4 like it is on component 3's form.

This probably comes down to the issue that in the relationships tab, connecting two unique ID's from one table to two coulombs of another table it creates a copy of the first table.

Any help on how to solve this would be greatly appreciated as I'm stumped.
Thanks

Which field(s) are you using to link your main form & subform?
What is different about the data for ID=3 & ID=4?

I am using the Component ID field for both of the relationships as two components from the components table will be connected together, this is what I am trying to show in the interface table.

I will upload a picture of the data in the interface table so you can see the differences
Thanks for the quick reply

you need to use a different query

I take it the the connection 'direction' is irrelevant i.e.

component A is connected to component B

is the same as

component B is connected to component A

rather than saying

component A uses component B (i.e. a parent/child relationship)

which is how your relationships and query is working at the moment

you can try using a cartesian query for your subform. Not sure what your sql is at the moment but a cartesian query does not have joins - so instead of a join, you would use a criteria something like

where componentID = connectedfrom or componentID=connectedto


the other alternative is to have two subforms - one for 'parents' and one for 'children'

Hi CJ
Thank you for your help, you really explained it well for me.
For future reference and for anyone else that may have this problem, I made a query to get the data and made a SQL code as follows:
FROM [Component List] INNER JOIN [Interface List] ON [Component List].[Componet ID] = [Interface List].[Connected from] or [Component List].[Componet ID] = [Interface List].[Connected to];
Then referenced this query in a form.
Thanks all