Group by Level2 BUT Total by Level3

[goodguy]

I have a query which returns records that can be grouped at four levels: Orders --> Products --> Components --> Parts --> Materials. The Materials records appears in the details section. I want to summarize Materials data (requirements and costs) at the upper group levels. What is happening right now is that the group totals are not grouping different material data but instead calculating the total lengths for all material types together.
The title above is the best way I can sum this up. Any help?

[apr pillai]

Display Group Header/Footer Sections of Group levels you want to take Summary. Add Textboxes and write Summary Functions (Sum(), Avg(), Count() whatever required) in the Group Footer or Header Sections.

[goodguy]

Thank you, @APRPillai for your response. In order to illustrate my specific need, please see the attached spreadsheet showing how I have calculated the sum of Materials grouped on OrderItem# in Sheet2 and grouped on Order# in Sheet3. This is what I want to recreate in Access reports.

Although this recordset is based on a long query joining 8 tables, I believe the query itself is not required for the purpose of my request. However, if needed, please let me know.

[goodguy]

Any help will be greatly appreciated.