Totals and grouping

[Extinct]

I have made some kind of billing database. I have a table clients. a table orders and a table orderdetails. On order level there are some values wheich are general for the order (e.g. discount)

To make the report I have a query which groups all these tables. the output is something like this
Client Order discount orderdetail
----------------------------------------
ClientA Order1 discount1 orderdetail1
ClientA Order1 discount1 orderdetail2
ClientA Order1 discount1 orderdetail3
ClientA Order2 discount2 orderdetail1
ClientA Order2 discount2 orderdetail2
ClientA Order2 discount2 orderdetail3
ClientA Order2 discount2 orderdetail4

No I try to make a report which has to calculate totals. For the total price of the order (on client level) I need to sum all the orderdetail prices (no problem here sum(orderdetail)) and show the total of discounts. This is the problem because sum(discount) gives 3*discount1+4*discount2

Anybody any idea how to solve this?

 

[LarryDeLaruelle]

Extinct:

This is a stab in the dark.

Have you tried doing the discount calculation in the query (assuming you are using one as the source)?

You could then do a sum([DiscountAmount]) in the footers to get an actual currency value.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[dhookom]

To total the DiscountAmount, you should create a group header on the Order field. Add a text box:
Name: txtDiscAmt
Control Source: [Discount]
Running Sum: Over All (or Group)
Visible: No

Add a text box to your report/client footer:
Control Source: =txtDiscAmt

This will not count is discount amount multiple times.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.