Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reporting sum 1

Status
Not open for further replies.

cthai

Technical User
Apr 19, 2011
53
US
Hello -

I have a report in Access 2008 and i have a call records and a field called Total, and another field called Status... in the total field i have numbers and in the status field i have [closed] and [open] i want to sum the total amount that was closed. how do i get the report to show me the total for all that was closed

example:

Records: Total: Status

Company A 3 Closed
Company B 4 Open
Company B 3 Closed


TOTAL CLOSED = 6
TOTAL OPEN = 4

 
You could have two expresions on your data source (query) one called 'iClosed' and one called 'iOpen' which have
Code:
iClosed : IIF(Status = 'Closed',1,0)

iOpen : IIF(Status = 'Open',1,0)

And then sum them in the report.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta
 
Hi 1DMF -

thank you for the respond. when i do the sum in my report i'm not getting the total number of close cases...i added the code you references above to my query. when i try to sum it in the report =Sum([iClosed]) i get 3 rather then the total number of closed,

sorry i'm not really good with reporting... I appreciates your help and patience
 
Create a textbox with a control source of:
Code:
=Sum(Abs([Status]="Closed") * [Total])
Do the same for Open.

If you potentially have more types of status values, consider using a subreport based on a totals query.

Duane
Hook'D on Access
MS Access MVP
 
Thanks Duane! that works!
 
It all depends where you are placing the sum, was it the footer or detail?

anyways, Duane knows best ;-)

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta
 
cthai . . .

Nothing to do with this thread ... but did you give up on thread705-1672958 ? See my post here (last entry) ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top