is it possible to repeat a query on data returned by the same query while returning it to a single table?

i have a query that runs on the following info:
PartNumber - the part number of an item
AsmNumber - the part number of the item the part belongs to
IsAsm - true/false on whether or not the part is an assembly

(see attached database)

the current query, when 11135 is used as input, returns 11124, 11165, 11103, and 11155. likewise when 11165 is entered the query returns 11109, 11110, and 11111.

i would like the query to return 11124, 11165, 11109, 11110, 11111, 11103, and 11155 when 11135 is entered.

the sample database attached is stripped down but accurately reflects what i'm working with. i do not know how many sub-assemblies belong to a top-assembly (could be zero, one, or sixteen), therefore i think the 'sub-query' would use the 'IsAsm' value as some sort of criteria - yes?


if what i'm asking is impossible, i'm very open to suggestions as long as they work with the data given.

i appreciate any help (especially being this is my first post) and apologize for the verbose post, i'm not good with brevity.

Stips,

I don't think a query (or set of queries) can help you if you don't know
the "depth" of your assembly tree.

How do you intend on displaying the results?

I don't use TreeViews, but that is what you really need here. To extract
the information you can use a pretty simple routine:


Public Sub cmdTest_Click()
Dim Assembly As Long
Assembly = InputBox("What Assembly")
GetTree(Assembly, 1)
End Sub

Public Sub GetTree(NodeNumber As Long, Level As Long)
Dim rst As DAO.Recordset

Debug.Print Space(3*Level) & NodeNumber

Set rst = "Select * From tblMain Where AsmNumber = " & rst!NodeNumber

While Not rst.EOF And Not rst.BOF
If rst!IsAsm Then
GetTree(rst!AsmNumber, Level + 1)
Else
Debug.Print Space(3*Level) & rst!PartNumberNumber
Exit Sub
End If
rst.MoveNext
Wend

End Sub


Just a rough idea, instead of the Debug.Print statements, you could insert
into a TreeView structure.

hth,
Wayne

the ultimate goal is to have the data in a table such that i can export the data to various documents.

i'm not famaliar with treeviews, but i'll give it a shot. thanks wayne.

Stips,

Well, it is in a table.

If you have "n-level" data, it has to either reside in "n" tables, or one table.

In one table, like your case, the data is OK, but there is no ready way of reporting it.

In "n" tables, when "n" is unknown, there can't be any queries, because there would be
no way of knowing how many queries would be required to present it.

How did you enter the assembly data in the first place?

Wayne

the assembly data was entered by users through a form and query. and yes, the n-level data is all in one massive table - the idea of filtering/querying by top-level number returning all children to a new (temp) table was definately an afterthought.

i completely understand that when 'n' is unknown there can't be any queries. it just seems that there should be a way of running the same query over and over until all "IsAsm"s have been returned to a new table - then again i'm just a novice access user.

i haven't had the time to investigate the treeview method (and honestly that's a new thing for me to learn), but i will give it a shot.

i appreciate the help wayne.