# Thread: get total in report

1. Registered User
Join Date
Mar 2004
Posts
660

## Unanswered: get total in report

I have special report. There are two headers, one is based on CustomerID another is based on TypeID. Each customer may order different type of product.There are 2 types ( 1 and 2)
i would like when the type is 1 count the total, otherwise the total is 0. In the typeID footer, i can get the total base on different type. for example,
customer 1
Type 1
total =99
Type 2
total = 0 (Always 0)

But in customerID footer, I would like to get the grandtotal (just type1 total) =99. How can i do this. Also i have question, how can i pass the value of total to customerID footer.

Thanks.

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
Use the same formula in the "total" textbox in the customerId footer as you have in the type footers. Access will give you the sum of values across the entire section.

3. Registered User
Join Date
Mar 2004
Posts
660
It didn't work. In the type footer, the total formula is: =IIf([Type]=1,Sum([Quantity]*[Price]),0)

But when i use the same formular in CustomerID footer, It is always display 0.

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
Probably because the type will never = 1 in the customerId footer. I thought you said you had type footers setup? why are you evaluating type number in the type footer?

5. Registered User
Join Date
Mar 2004
Posts
660
yes, i have type footer. But it is grouped by the type. Please see the report.

DateSent: 11/22/2005
Business Information
Las tName: c Address: c
First Name: c City: c State: c
__________________________________________________ _____________
Type: 1
Title 04 \$16.00 4 \$64.00
Title 32 \$27.00 1 \$27.00
Subtotal: 91
SubtotalTemp 91
_______________________________________________
Type: 2
Maintenance Handbook \$9.00 6 \$54.00
Control of Ionizing Radiation \$78.00 4 \$312.00
Subtotal: 366
SubtotalTemp 0

_______________________________________________
GrandTotal \$457.00
GrandTotalTemp \$91
__________________________________________________ ____

The last one : TotalTemp \$91 is what i need.
Please help me again!Thanks.

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
So type 1 is the only thing you want the total for in the subtotaltemp field, ever?

sum(iif([type] = 1, Sum([Quantity]*[Price]),0))

7. Registered User
Join Date
Mar 2004
Posts
660
that is what i did in the typeID footer. But how could i get the grandtotaltemp? i use the same fomular in the customerID, it didn't work.

8. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
Not quite, look very closely at the code you provided vs. the code I provided. You'll notice there is a subtle difference.

9. Registered User
Join Date
Mar 2004
Posts
660
I tried the code you gave to me. It got error message:

Cannot have aggregate function in expression
(sum(iif([type] = 1, Sum([Quantity]*[Price]),0))).

10. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
Oh yeah, forgot about that. The easy solution would be to perform the multiplication in the query, then sum your new aggregate field.

11. Registered User
Join Date
Mar 2004
Posts
660
I don't think this is easy way for me. My query relatates to five tables. How could ust aggregate field. Also one customer may buy different type of product. For each type, there are different items.

12. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
Uhhhh... ([Quantity]*[Price]) As yourNewField?

13. Registered User
Join Date
Mar 2004
Posts
660
yes, you gave me a clue. Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•