Would you like to improve my Query

[ac916]

Hi,


I have a Query which used to construct indented Bill of material from a single level bom table.
(Parent, Component, qty, OpSeq & ItemSeq) to a query result (Top FG, Subassembly, Parent, Component, Qty,
OpSeq & ItemSeq).

Current query has limitation of creating duplicate records and it's can only go up to 5 queries only.


Would you like to take the challenge to improve my Query.
Attached an access file (Bill.mdb).

Logic :

1. If a parent not exist in any Component, then it is a Final Top FG
2. Take the Component to validate against Parent until can't find any Parent,
3. If any parent start with 844 or X855 (X refer to any character) then put it under Sub column.


Look at the result of Q5, this is the result. However, it has duplicate records which I need to get rid of.

Enclosed an Access file (Bill.mdb).

Thanks
Andrew

 

[cjard]

if it was in Oracle i'd tell you to use a CONNECT BY statement.. I dont use Access, sorry