Hierarchy Query 1

[striker83716]

I have a table that was Imported, the structure of the table leaves a bit to be desired. What I would like to do is create a "make table" query to get the desired results. Here is my trouble, my table has a part number and a "level" I neet to show the parent instead of the level i.e curent table

ID Part Level
1 1000 1
2 4000 2
3 3000 3
4 5050 2
5 3020 3

I want the query results to look like this
ID Part Level Parent
1 1000 1
2 4000 2 1000
3 3000 3 4000
4 5050 2 1000
5 3020 3 5050

Basically In the parent field I am looking for the part number with the highest record ID (lowere then the current record ID), that is 1 level lower then the current record.

 

[PHV]

Something like this ?
SELECT A.ID, A.Part, A.Level, B.Part AS Parent
FROM yourTable AS A LEFT JOIN yourTable AS B
ON B.ID=DMax("ID", "yourTable", "ID<" & A.ID & " And Level=" & A.Level-1)
;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244