Combining tables

[JohnW]

Hi,
I trying to produce a query where I'm trying to combine two tables -
1 master list of all parts manufactured, price and their code number &
1 list of an assembly that has lists some of the parts in the master list
with description

Obviously, the relationship is the parts code number.

The result I'm getting is the list of components used in the assembly with a
description and price.

What I'm after is a report with the master list with all parts in existence
with a description against the parts used in the assembly. The aim is to
produce a report to list which parts are used and unused. Eventually more
assemblies may be introduced.

Any ideas?
I'm thinking of an "And/Or/Exclusive" expression but cannot find it anywhere!

Thanks for looking

 

[Tom Lake]

Hi,
I trying to produce a query where I'm trying to combine two tables -
1 master list of all parts manufactured, price and their code number &
1 list of an assembly that has lists some of the parts in the master list
with description

Obviously, the relationship is the parts code number.

The result I'm getting is the list of components used in the assembly with
a
description and price.

If you're using QBE (Query by Example), click on the line that connects
the two tables and choose Properties. Now try the various choices such as
selecting the choice that allows all records from the master table and only
those records in the other table that match. One of those choices might
give you what you want.

Tom Lake
that match.

 

[JohnW]

Thanks for that, looks like I looked under every rock except that one.

 

[Jerry Whittle]

You eventually may need a third table. I'm assuming that a part can be used
on many assemblies and, naturally, and assembly has many parts. If you are
just seeing what parts are used by an assembly, your query will work.

However if you also need to see what assemblies use a certain part, think of
going the other direction, you could have a problem.

This is usually fixed by adding a third bridging or linking table between
Parts and Assemblies. This third table has fields for PartNo and AssemblyID.
This combination could be the primary key for the table or at least a unique
index to prevent duplicates. You could also have a field for PartCount as an
Assembly could use dozens of the same screws for example.

With this bridging or linking table, you can ask questions in both
directions such as this assembly uses which parts or this part is used on
which assemblies.