Run query or report from multiple tables

[Sim]

I have three tables:

Equipment
Instruments
System Numbers

Each record in the tables 'equipment' and 'instruments' has three lookup
fields that link to fields to the 'system numbers' table.'

How can I create a query or report that lists all records contained within
the equipment and instruments tables that are linked to the system numbers
table?

 

[John W. Vinson]

I have three tables:

Equipment
Instruments
System Numbers

Each record in the tables 'equipment' and 'instruments' has three lookup
fields that link to fields to the 'system numbers' table.'

How can I create a query or report that lists all records contained within
the equipment and instruments tables that are linked to the system numbers
table?

By forgetting that Lookup Fields ever existed (since they shouldn't have).

Create a Query joining Equipment to *three instances* of the System Numbers
table - add the table to the grid three times, joining each instance to the
appropriate field.

Since there is apparently no relationship between the Equipment and
Instruments table, you may need a UNION query with a similar query joining
Instruments to three instances of System Numbers. See the online help for
UNION.

If instruments and equipment are in fact related, please explain how...

John W. Vinson [MVP]

 

[Sim]

The only relationship between the instruments and equipments field is the
lookup fields contained in the System No's Field.

E.g. The System Numbers table has two fields that are linked to all tables
within the database:

1. System No
2. Sub-System No

So each record in the instruments and equipments tables must relate to both
fields - system and sub-system (hence the lookup). The system and sub-system
numbers would be the only fields that relate the instruments and equipment
tables though.

What I am trying to create is a report that will list all instrument and
equipment numbers by System Number and/or Sub-System No.

Hope I've explained better ... ...

 

[Michel Walsh]

Bring SystemNumbers, Instruments and Equipments table in a new query. JOIN
the fields SystemNo and SubSystemNo from SystemNumbers to the same field in
Instruments. Right click on each of the two lines showing the join, to edit
the join, to keep all records from SystemNumbers. Repeat the two-fields join
between SystemNumbers and Equipments (and again, edit BOTH lines, else you
get an error) to keep all records from SystemNumbers.

Bring the fields you want from the tables. If an instrument or an equipment
has no matching (SystemNo, SubSystemNo) it won't be listed. If SystemNumbers
has a (SystemNo, SubSystemNo) which is not found in Equipments, fields
picked from Equipments will be all null.


Hoping it may help,
Vanderghast, Access MVP

 

[Sim]

Thanks Michel - that works.

Is there a way I can link another table - call this one P&ID - which similar
to the SystemNumbers table - has a link to each record in the Instruments and
Equipments tables?

I tried to add it into the query the same as the joins to the SystemNumbers
table but Access doesn't like it!

 

[Michel Walsh]

Make another query?


Or, merge the two tables, vertically:

SELECT SystemNumber, SubSystemNumber, "SystemNumber" AS origine FROM
SystemNumbers
UNION ALL
SELECT SystemNumber, SubSystemNumber, "P&ID" FROM [p&ID]


save that query as, say, qu1, and then, use qu1 instead of SystemNumbers in
the query that involves only SystemNumbers



Hoping it may help,
Vanderghast, Access MVP