Show all unlinked parts with query

[mischa]

I was wondering how to calculate all records that have not yet been linked to other records. For example: I have 1 Product, which is linked with multiple components which is subsequently linked to subcomponents. All these types can be linked to Alternate Parts.

In order to reduce redundant information I would like to show the unlinked parts to the user. Therefore I would like to make a query which shows all unlinked parts.

I have the following tables with subsequent PK Part Number field values:
tblProduct->ProductPN
tblComponent->ComponentPN
tblSubComponent->SubComponentPN
tblAltParts->AltPartPN

I am unable to find a appropriate query structure with criteria for this issue due to the many different fields required and the fact that if in one query all these tables are shown no data will be shown

I hope someone could give me some advice on how to start.

Thanks in advance!

 

[CazB]

What links each table to the next?

does tblComponent link to tblProduct using ProductPN and then does tblSubComponent link to tblComponent using the ComponentPN... and so on down the line?

Can you add a pic of your relationships?

 

[mischa]

CazB, Thanks for the quick reply!

Yes these tables are related with each other but with a relationship table in between. I attached a screenshot of a part of the table relationship related to this question.

 

[jdraw]

mischa,

Work through this tutorial to understand tables and relationships,

Good luck

 

[mischa]

mischa,

Work through this tutorial to understand tables and relationships,

Good luck

I already have a complete Relation Schematic for my database. Therefore I do not know what I should change in the design in order to fix my problem, personally I do not think that changing the design of my tables and relationships would solve my question.

Isn't it possible to solve this problem by using Queries?

 

[jamesdpmullan]

Hi, not sure if this helps however double click on the lines within the relationship, select Join Type followed by Include ALL records from (A) and those records from (b) where the joined fields are equal

Hope this helps