Reporting sum 1

[cthai]

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

 

[1DMF]

You could have two expresions on your data source (query) one called 'iClosed' and one called 'iOpen' which have

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

 

[cthai]

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

 

[dhookom]

Create a textbox with a control source of:

=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

 

[cthai]

Thanks Duane! that works!

 

[1DMF]

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

 

[TheAceMan1]

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]
Also faq181-2886 [blue]Worthy Reading![/blue]