Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    56

    Unanswered: Sum within a report

    This one should prob be easy for you access pro's whereas I am not...yet.

    Simply put, within a report I have a field populated with numeric data such as 12,26,52. These numbers represent length of contract commitment in weeks. In a sort order these are listed and broken down by sales office. Essentially I may have say 3 listed, under a sales office of say 12,12,52, and what I want is a field in the report detail that sum's the number of contracts in this example 2:12 week contracts and one 52.


    Make sense? Hopefully, thanks you ahead of time.
    Last edited by roma92; 06-07-04 at 15:44.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I think you could make another query that counts those numbers and use it as a subreport.
    ghozy.

  3. #3
    Join Date
    Feb 2004
    Posts
    214

    ...

    create an unbound text box and type in that text box =Sum([Field])

    Field will be the name of the field that you are getting the sum from.

    Hope this works!

  4. #4
    Join Date
    Mar 2004
    Posts
    56
    I cannot use a subreport as they want it all on one report, as for the sum, if I use that formula it will total the 12, 12, 52 and give me 76 where as I need one field saying there is 2 12's and one 52.

  5. #5
    Join Date
    Mar 2004
    Posts
    56
    I have made the formula =IIf([CNCOMM]="52",Count([CNCOMM]),"") but that doesnt seem to be "seeing" the 52 to count.

    I am sorry, I should have mentioned that i am using Access 2003

  6. #6
    Join Date
    Mar 2004
    Posts
    56
    Actually I think I see what its doing or rather whats wrong with my formula, since it meets the if statment true and sees the 52 its counting all the fields to total 3 (12,12,52)rather than seeing the 52 and counting 1 as there is only one 52

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    if your report is not so complex or not so big, go with DCount() function I say. If you Dcount() in a query instead whole table it wouldn't cause too much problem.
    ghozy.

  8. #8
    Join Date
    Mar 2004
    Posts
    56
    I am confused on the Dcount expression, if my field is [CNCOMM] and my table is [PCONTMST_Active] how would I write the expression within the report to count only the records that contain a 52?

    My report is fairly large, there are 3500 records

  9. #9
    Join Date
    Mar 2004
    Posts
    56
    Actually, I tried the formula =DCount("[CNCOMM]","PCONTMST Active","52") and got the number 2030, problem being is I am sorting by sales office and it looks as though it calculated the total number of 52's rather than just the ones under the particular sales office within the header.

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    with dcount you make a global table count. it is not based on your report query.
    lets see:
    =DCount("[CNCOMM]","PCONTMST Active","52")

    in criteria section of function you should enter a valid WHERE clause there (without WHERE keyword) to calculate your count. Like;
    =DCount("[CNCOMM]","PCONTMST Active","[CNCOMM]=52 AND [SALES OFFICE]=4")
    I don't know your table structure though. That was just an example. and for sales office field you can use the data on your report instead of absoulte numbers like 4.
    ghozy.

Posting Permissions

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