Quotation sheet - sub-assemblies rolled up into top level

[pinebush]

To create a quotation, I need the sub-assembly items to be listed on the quote sheet, but the prices for each sub-item should be aggregated and rolled up to the main assembly line. I have attached a pdf which shows the desired result.


Any of the items in the product table can be treated as a sub-item. It's not that it is predetermined that SubItem 02 is always part of ParentItem 02. The pdf shows an example of a kitchen countertop, where there is a base product, then extras for special edging, backsplash, dimensions, etc. It seems like maybe I need to click an 'Add' button on the subform line which I want to be considered the parent item, which might then pop up another form (subform) where I can enter the sub-items. Even if this is the case, I am not sure how to aggregate the prices into one line.
Attached: 1) pdf file with screenshot examples and desired outcome; 2) functioning database with a minimal number of records

[xps35]

I don't think you have the option in your data model to link Subitems and Parentitems to each other. As long as you don't have that, you can't include it in a report.
How you model that depends on the number of levels you want to use.

[mike60smart]

You are making what is normally a very easy process very complicated.

Why do you want to group all sub related Totals into 1 final Sum?

[pinebush]

We have found that it works best if the client is not given the detailed breakdown of each component within the project. At that point they begin nit-picking and challenging the price. It is preferred that we show only the pricing for the grouped components, and include the list of each subitem, so that the client knows that our quotation is complete.
I am open to other suggestions - this is the only method that I have thought of. It is entirely possible that I am over complicating an otherwise simple process. What is a simple approach? Maybe if I get pointed in a different direction, it will all make sense to me.

[orange]

See this post and related thread dialog for an explanation/description/discussion on parts/assemblies;
parts/kits; assemblies/subassemblies. It may help with concepts and approach.

[kd2017]

We have found that it works best if the client is not given the detailed breakdown of each component within the project. At that point they begin nit-picking and challenging the price. It is preferred that we show only the pricing for the grouped components, and include the list of each subitem, so that the client knows that our quotation is complete.
I am open to other suggestions - this is the only method that I have thought of. It is entirely possible that I am over complicating an otherwise simple process. What is a simple approach? Maybe if I get pointed in a different direction, it will all make sense to me.

I completely agree with your assessment. Subcontractors can't afford to price match like a Walmart.

Check out oranges links. Research hierarchical relationships. Search for BOM example data models.

Nice UI!

[pinebush]

I may be able to implement some concepts from 'orange'. I'm still not there yet, but it gives me some direction.
Thanks for your input.