Results 1 to 13 of 13
  1. #1
    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. #2
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    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. #4
    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?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    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. #6
    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))
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    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. #8
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    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. #10
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    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. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uhhhh... ([Quantity]*[Price]) As yourNewField?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    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
  •