# Thread: Sum within a report

1. Registered User
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 14:44.

2. Registered User
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.

3. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
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.

8. Registered User
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. Registered User
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. Registered User
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.

#### Posting Permissions

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