Query question

[jerrypeco]

Sorry. Should have been more specific. On the main reports, I need to show a subtotal from the OrderDetails subreport and a subtotal of the FreightCharges. They have to be combined to create a grand total.

[JoeM]

Right, that is what I was talking about in my past two posts. I am not sure if it is possible to get values from your Subreport to sections of your Main Report so that they can be added together. That is why I suggested creating an Aggregate Query to get the totals for all the Order Details, and include that in the Main query that the Main Report is based on. Then that total will be available to you in the main report section.

[jerrypeco]

Let me try to clarify the last thread. I need to show each item and its cost in the OrderDetails subreport and total them at the bottom (footer).

[JoeM]

Let me try to clarify the last thread. I need to show each item and its cost in the OrderDetails subreport and total them at the bottom (footer).

We're still talking about the same thing here. Let me try to break it down for you.

First, forget about the Subreport of the Order details, let's just focus on Main Report.

1. First, we create an Aggregate Query of the Order Details that sums all Costs by Order ID. So when complete, we will have a query that lists each Order ID once, and the total Cost (sum of all the Order Details per Order ID).
2. Create a query between Customers, Orders, and the Aggregate Query created above. They should all be "1-1" relationships. On this report, you can create a calculated field that adds the Freight charges to the Total Order ID charges from the Aggregate Query. This is the total number your are looking for.
3. Create the Report based on the query above. This should give you everything you want, except for the details of each individual Order Detail record.
4. Now create your Order Detail Subreport (as talked about in previous posts).
5. Imbed your Subreport into your Report, matching on the Order ID field. This will now give you report the detailed of the Order Details it needs.

Does that help make things clearer?

[jerrypeco]

The report is coming out correct now. Thank you!

Just one minor thing though. On the section that contains the OrderDetail subreport, I need to see the items in the order they were entered.

In the OrderDetails table, all items are sorted by Order Number and then OrderDetail ID, sequentially. However, randomly on the report, the order is jumbled. Is there an easy fix for this?

I really appreciate your help!!

[jerrypeco]

Nevermind. Just took care of it. Thanks again for all of your help!!!