Query to list components after product ordered (repost)

[KatBaroo]

Hi,



after trying to get an answer previously - which led me in different direction with the database, I reworked the database and found the comments really helpful.

Now I am back with the same problem:
My database deals with components (Items) and products (sub units). Each item can be ordered individually. Each sub unit can be order. And someone can order combination of both.

Each subunit is made up of items - varying quantities.

I have set up 1 table for the ordering of items and 1 for the ordering of sub units.

Now I need to get the subunits listed as a) sub unit with quantitiy of the individual items.

I have a table for the components in the subunit (tbl_SUComponents) but are not successful in listing the items based on the quote. I have tried in qryQuoteSubUnitItems but regardless of which way I set the joint it just won't work.

Sorry, I am only basic in sql as in following instructions.

I have the database attached for anyone wanting to check it out.

Thank you to everyone for looking at the post and trying to help me.

Katbaroo - Saving yakking Yaks
Orderbook - Forum Query 2021.12.20.zip

[June7]

Assembly/manufacturing (along with family tree) are the most difficult type db to build. It's because it can involve recursive relationships which Access doesn't handle well.

Common topic of numerous forums and many examples have been posted.

[orange]

Further to June's advice, you may find some guidance by researching hierarchies. You may also find reading posts/threads on assemblies/kits and parts.
Here is a link that may offer some insight. Another

[KatBaroo]

Thank you to everyone who replied - I hope you had a good time over the holidays. I apologise for the delay but the Christmas rush just got totally mad.