conditional sum needed? sums not totaling correctly...

[Guest]

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.

 

[Marshall Barton]

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?

Add another text box (named txtRunFreight) to the order
footer section. Bind it to the freight field and set its
RunningSum property to Over Group.

Then you can use a text box with the expression
=txtRunFreight in the date group footer to display the total
fright for the day. The total orders for the day would be:

=Sum([UnitPrice]*[Quantity]) + txtRunFreight

 

[Guest]

That worked like a charm!
Thanks for your help!!!