Would like 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.

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.

Thanks
Andrew

 

[Banana]

You could try and read up on how to create joins; that will take care of 1 and 2. Also, you can look into SELECT DISTINCT to return only unique values if necessary.

As for 3, I think you need a separate query.

 

[ac916]

Thanks Banana

Hi Banana,

Thanks for your input, I am just a beginner, probably you can help to highlight my SQL and point out or enhance it.

Thanks.

 

[ac916]

Enclosed with the mdb file

Enclosed with the mdb file.