Query to display all related data from 4-level tree

[drewship]

Hello all. I have a table similar to this:

UnitID Unit ParentID

1 ship
2 engine 1
3 radar 1
4 oil 2
5 gears 2
6 transmitter 3
7 receiver 3
8 waveguide 6

The UnitID is the Key, Unit contains all possible units, and the ParentID
indicates the UnitID of the next higher Unit on the tree.

How do I connect the ParentID with the UnitID when a Unit is selected from a
combobox? For example, if I select receiver, I need to display transmitter in
a separate field. If I select waveguide, I need to see transmitter in one
field and radar in another field. I hope this makes sense and someone can
help me.

Thanks,
Andrew

 

[S.Clark]

Assuming that you're displaying this on a form, the controlsource for the
Parent field could be:

=Dlookup("Unit","tblUnits","UnitID=" & txtParentID)

 

[drewship]

Thanks S.Clark. I am not sure I understand what you recommend.

I have the following query which allows me to select and display the unit
name:

SELECT Unitstbl.UnitID, Unitstbl.Unit, Unitstbl.ParentID
FROM Unitstbl
ORDER BY Unitstbl.Unit;

The Unit is displayed on the form in the UnitID combox. I also have a
Parent3 textbox to display the next level up the tree, a Parent2 textbox to
display the next level up the tree, and a Parent1 textbox to display the
overall parent.

If I understand correctly, you want me to put

=DLookUp("Unit","Unitstbl","UnitID=" & [ParentID])

in the Control Source of Parent3. I can follow that the DLookUp is looking
at the Unit column in the Unitstbl, but what is "UnitID=" & [ParentID] doing?
I get a #Name? displayed in the Parent3 textbox.

Thanks,
Andrew

 

[KARL DEWEY]

Try this --
SELECT drewship.Unit AS Selected, drewship_1.Unit AS Parent, drewship_2.Unit
AS Items
FROM (drewship INNER JOIN drewship AS drewship_1 ON drewship.ParentID =
drewship_1.UnitID) INNER JOIN drewship AS drewship_2 ON drewship_1.UnitID =
drewship_2.ParentID
WHERE (((drewship.Unit)=[Forms]![YourFormName]![Combo4]));

 

[drewship]

Thanks Karl!! This is a cool piece of code and I think I understand what is
doing, but I am unable to get it to work. Here is what I have changed so it
uses my actual tables and forms:

SELECT Units.Unit AS Selected, Units_1.Unit AS Parent, Units_2.Unit AS Items
FROM (Units INNER JOIN Units AS Units_1 ON Units.ParentID=Units_1.UnitID)
INNER JOIN Units AS Units_2 ON Units_1.UnitID=Units_2.ParentID
WHERE (((Units.Unit)=Forms!Assets!UnitID));

Units is the table that has every unit to select from in the column Unit, as
well as the ParentID column and UnitID column. The combobox is UnitID on the
Assets form.

I have tried the following -
1. Created a query and used it as the rowsource for the combobox.
2. Pasted this directly into the rowsource of the combobox.

Neither is doing more than displaying a single blank line. I will keep
trying other combinations, but I am not very good with querys so any
assistance is greatly appreciated.

Thanks,
Andrew

Try this --
SELECT drewship.Unit AS Selected, drewship_1.Unit AS Parent, drewship_2.Unit
AS Items
FROM (drewship INNER JOIN drewship AS drewship_1 ON drewship.ParentID =
drewship_1.UnitID) INNER JOIN drewship AS drewship_2 ON drewship_1.UnitID =
drewship_2.ParentID
WHERE (((drewship.Unit)=[Forms]![YourFormName]![Combo4]));

--
Build a little, test a little.

Hello all. I have a table similar to this:

UnitID Unit ParentID

1 ship
2 engine 1
3 radar 1
4 oil 2
5 gears 2
6 transmitter 3
7 receiver 3
8 waveguide 6

The UnitID is the Key, Unit contains all possible units, and the ParentID
indicates the UnitID of the next higher Unit on the tree.

How do I connect the ParentID with the UnitID when a Unit is selected from a
combobox? For example, if I select receiver, I need to display transmitter in
a separate field. If I select waveguide, I need to see transmitter in one
field and radar in another field. I hope this makes sense and someone can
help me.

Thanks,
Andrew