Relation between tables

[ssayan79]

Hi,

this is my first post in this forum, and I do hope I won't make too much mistakes in it.

I have a problem building relationship between table in ACESS.

I have two tables, BOM and Coils, the former containing elements of the latter.



When I create the Relation between them (Picture 1), it works fine and, opening the table Coils, I'm able to see where two of the four elements are used. (Picture 2)

My scope is to see where all the four elements of the table Coils are used.

So I created the relation as shown in Picture 3 and Picture 4.

However it does not work, as I lost the results shown in Picture 2 and I'm not able to get what I expected.

Could you please support me?

Thank you all.

[CJ_London]

It looks to me like you need option 3 - include all from BOM (otherwise known as a Right join). At the moment you are including all from Coil (a left john).

Note that joins and relationships are not the same thing - although they look similar in presentation. Joins are used in queries, relationships define how the data is managed in the relationships window. When you create a query, the relationships become joins and are displayed as such, but can be changed or removed. e.g. click on a join property in a query and you will not see the 'enforce referential integrity' option.

Normally relationships are just normal inner joins (option 1), there are very few occasions (I can't even think of one right now) when you would want to use options 2 or 3 - you do that in the query.

[orange]

It might be helpful to readers if you gave us a description of the business. BOM usually means Bill of Materials and often involves a Hierarchical structure. You use of BOM may not.
Here is a link to an article involving a hierarchy which may be of some use to you.
Access does not play well with field names that have embedded spaces.

Welcome and good luck.