Equipment/Sub-Equipmet/Signal query

[Ed]

I have a Equipment -to-Signals query with 3 tables:
tblEquipment
tblEquipTypes
tblEquipTypesSignals

EquipTypeID is in all three tables.

This is a many-to-many table arrangement. It creates a signal list based on
the number of equipment and the type of equipment. Some equipment types have
more signals than other types. This is a development system where the number
of signals per equipment type changes.

It works great! However, one equipment type (a motor) has 1800 signals. My
boss wants to divide this into several sub-equipment types but still list it
as one equipment. Signal maintenance would be troublesome if I have to go in
and change the same signal in many sub-equipment types. This motor has 49
sub-equipment (made up of 3 different types: 24 , 24, and 1).

How can I add an equipment type that may be made up of several signals or
may be made up of several equipment types?

SELECT tblEquipment.EquipName, tblEquipment.EquipTypeID,
tblEquipTypes.EquipType, tblEquipTypeSignals.EquipSignal
FROM (tblEquipTypes INNER JOIN tblEquipment ON tblEquipTypes.EquipTypeID =
tblEquipment.EquipTypeID) INNER JOIN tblEquipTypeSignals ON
tblEquipTypes.EquipTypeID = tblEquipTypeSignals.EquipTypeID;

Thanks,
ed

 

[PC Datasheet]

How about ---
TblEquipCategory
EquipCategoryID
EquipCategory

TblEquipType
EquipTypeID
EquipCategoryID
EquipType

TblEquipTypeSignal
EquipTypeSignalID
EquipTypeID
EquipTypeSignal

TblEquipment
EquipmentID
Equipment
EquipTypeSignalID

EquipType is categorized so consequently EquipTypeSignal is categorized.
Also as long as you have an EquipTypeSignalID for a piece of equipment, that
piece of equipment is categorized and typed through EquipTypeSignalID.

 

[Ed]

This seems only to categorize the signal into different types of signals.
The output of the query is the number of signals, which should change if one
equipment has multiple sub-equipment. e.g. if I list the motor, I want the
query to list the signals for all associated sub-equipment without my having
to list each sub-equipment in tblEquipment.

ed

 

[PC Datasheet]

Add another table:

TblSubEquipment
SubEquipmentID
EquipmentID
SubEquipment
EquipTypeSignalID

If a piece of equipment has no subequipment, base your query on
TblEquipment. If a piece of equipment has subequipment, base your query on
TblSubEquipment.

Steve
PC Datasheet

 

[Ed]

I am expecting to create one query to display all signals from all equipment
and sub-equipment at the same time!

ed