Hierarchy Query 2

[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 (lower then the current record ID), that is 1 level lower then the current record.

 

[RickSpr]

I had a similar need once when I wanted to retain the sequence of rows imported from a text file, where the key did not reflect that sequence.

I don't think you can do this entirely in a query. However, you can do it by using the following function:

Public Function ParentPart(Part As Variant, Level As Integer) As Variant
    Const MaxLevel = 50   ' change to what's reasonable
    Static PartHierarchy(1 To MaxLevel) As String

    PartHierarchy(Level) = Part
    If Level = 1 Then
        ParentPart = Null
    Else
        ParentPart = PartHierarchy(Level - 1)
    End If
End Function

In your MakeTable query, use the expression ParentPart([Part], [Level]) as the source for the Parent field.

Note that this will generate incorrect results if you switch the query (any kind) to Datasheet View. The reason is that this function is dependent on being passed the rows in the correct sequence. In Datasheet View, Access sometimes reads rows more than once, or backs up and re-reads rows when you scroll up.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[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