Hierarchical Sort With Query?

[palookavile]

Is it possible to sort a multilevel hierarchy table (like bill of materials) that has a partID, parentpartID and level field with just a query?
So let's say i have a main assembly on top, then it has its subassemblies and parts in level 2, subassembly in level 2 can have other subassembies or parts in level 3 and so on...

I don't need a visual treeview, just to sort the records in a way that it finds parts with no children in first level, lists them, then finds first subassemby in first level and lists parts that belong to it,
and then it looks for sub-subassemblies and so on until all parts are listed.

This is the sort i need:

part.......parent......lvl
-------------------------
asm......................0
part1.......asm.......1
part2.......asm.......1
sub1........asm.......1
part3.......sub1......2
part4.......sub1.......2
sub2.......asm........1
part5.......sub2......2
part6.......sub2......2
sub3.......sub2.......2
sub4........sub3......3
part7...... sub4......4
...

So is there an easy way to do this in a query, or if you could point me to a procedure that does this?

I hope this makes sense, sorry for my english, i'm a bit rusty.

 

[John Big Booty]

The sorting Hierarchy is determined by the order of the fields in your Query from left to right, in the design grid..

However you could decide to display those fields in a different order in your form.