I'm out of ideas. Help please

[Jason F]

I have a database that tracks a lot of information on an electrical
distribution system. I have an Equip_table that list all equipment in the
database. Each record defines a piece of equipment. Each record has an
Recrd_ID which is an autonumber and a unique device name. Each record
defines the device that it is feed from using the Recrd_ID and is saved in
field Feed_From. I'm trying to create a query of all devices fed from Main
Switch Board Recrd_ID "1" and name "MSBA". the problem I'm having is that
some devices are three levels and sometime up to ten levels deep. and exaple
is. "RTU-1" is feed from "Panel A" and panel A is feed from "MDPA" and mdpa
is feed from "MSBA" how can i generate a query that list all equipment feed
from "MSBA" only? The database currently has near 1,000 devices defined.

 

[Bob Barrows [MVP]]

I have a database that tracks a lot of information on an electrical
distribution system. I have an Equip_table that list all equipment
in the database. Each record defines a piece of equipment. Each
record has an Recrd_ID which is an autonumber and a unique device
name. Each record defines the device that it is feed from using the
Recrd_ID and is saved in field Feed_From. I'm trying to create a
query of all devices fed from Main Switch Board Recrd_ID "1" and name
"MSBA". the problem I'm having is that some devices are three levels
and sometime up to ten levels deep. and exaple is. "RTU-1" is feed
from "Panel A" and panel A is feed from "MDPA" and mdpa is feed from
"MSBA" how can i generate a query that list all equipment feed from
"MSBA" only? The database currently has near 1,000 devices defined.

So you have a self-referencing table and the 3 relevant fields are
Recrd_ID - PK autonumber
DeviceName - Text
FeedFrom - Number - related to Recrd_ID

This is a nice, compact design, but unfortunately, it makes it very
difficult to query for greatX grandchildren.
It's going to need to be a union query that starts like this:

SELECT Recrd_ID,DeviceName FROM Equip_table
WHERE Recrd_ID = 1
UNION ALL
SELECT Recrd_ID,DeviceName FROM Equip_table
WHERE FeedFrom = 1
UNION ALL
SELECT Recrd_ID,DeviceName FROM Equip_table
WHERE FeedFrom IN
(SELECT Recrd_ID FROM Equip_table WHERE FeedFrom = 1)
UNION ALL
SELECT Recrd_ID,DeviceName FROM Equip_table
WHERE FeedFrom IN
(
SELECT Recrd_ID FROM Equip_table WHERE FeedFrom IN
(SELECT Recrd_ID FROM Equip_table WHERE FeedFrom = 1)
)
UNION ALL
SELECT Recrd_ID,DeviceName FROM Equip_table WHERE FeedFrom IN
(
SELECT Recrd_ID,DeviceName FROM Equip_table WHERE FeedFrom IN
(
SELECT Recrd_ID FROM Equip_table WHERE FeedFrom IN
(SELECT Recrd_ID FROM Equip_table WHERE FeedFrom = 1)
))
UNION ALL
SELECT Recrd_ID,DeviceName FROM Equip_table
WHERE FeedFrom IN
(copy in the previous select statement in the union query,
removing the DeviceName field from the outer SELECT
clause)
etc. until you get all ten levels