Hello.
I have a customer order database I'm working with.
I'm trying to create a Report which will display the following.
lastName
orderID
orderDate
orderTotal
Each order is associated with an orderDetail.
Each orderDetail has an orderDetailID.
When I create a report to try to sum the totals for each month, it sums the FreightCharge for every orderDetailID.
For example, say an order (orderID 10) has 5 items (orderDetailID's 26-31).
This order has 1 FreightCharge of $5.00.
When I total my order in the "orderFooter" of the report (=Sum([UnitPrice]*[Quantity])+[FreightCharge]) it gives the correct total for the order.
HOWEVER, when I total my order in the "orderDateFooter" of the report (=Sum([UnitPrice]*[Quantity])+[FreightCharge]) it gives an INCORRECT total.
The total it is giving is the correct orderTotal + the FreightCharge *5.
It is adding the FreightCharge 5 times, one for each orderDetailID instead of ONLY one for the orderID.
How would I limit the sum to only use the FreightCharge once for each order in the calculation instead of once for each item in the order?
You're help is greatly appreciated as I have been trying to resolve this issue for 3 straight days to no avail. I'm sure it's an easy fix, I think I'm just over analyzing it.
I have a customer order database I'm working with.
I'm trying to create a Report which will display the following.
lastName
orderID
orderDate
orderTotal
Each order is associated with an orderDetail.
Each orderDetail has an orderDetailID.
When I create a report to try to sum the totals for each month, it sums the FreightCharge for every orderDetailID.
For example, say an order (orderID 10) has 5 items (orderDetailID's 26-31).
This order has 1 FreightCharge of $5.00.
When I total my order in the "orderFooter" of the report (=Sum([UnitPrice]*[Quantity])+[FreightCharge]) it gives the correct total for the order.
HOWEVER, when I total my order in the "orderDateFooter" of the report (=Sum([UnitPrice]*[Quantity])+[FreightCharge]) it gives an INCORRECT total.
The total it is giving is the correct orderTotal + the FreightCharge *5.
It is adding the FreightCharge 5 times, one for each orderDetailID instead of ONLY one for the orderID.
How would I limit the sum to only use the FreightCharge once for each order in the calculation instead of once for each item in the order?
You're help is greatly appreciated as I have been trying to resolve this issue for 3 straight days to no avail. I'm sure it's an easy fix, I think I'm just over analyzing it.