help with getting sums or counts to work in report

[siouxprincess]

Hello,

The table I work with is my employment's help desk database. I need to add up each of the values of the usr_origin field and the total of the usr_elaspedtime field that corralates with each value of the usr_origin field.

There are 16 different values for the usr_origin.

As an example, when I try to add up how many "Internal Software Errors", I get a negative -1 (and I really want is a positive 1). Formula I was using is: =sum([usr_Origin]="Internal Software Error")

More info of how the report is designed:

Page header has the headings in it.
Detail has the information relating to the headings in it.
Page footer has date and page in it.
Report footer has the columns:

"internal software error", sum (example: -1 that I want to be a positive 1), next column would be the total of the usr_elapsetime for the 1 internal software error tickets.​

The query setup as:
dept, ticket #, open date, closed date, origin (group by), summary, detail, solution. ***I've tried to add a second usr_Origin (count) in the query, I get an "ODBC -- call fail" error when I run the report.***

I haven't tried tackling formula for total sum of usr_elaspedtime for "Internal Software Errors" (I'm clueless on this one).

Any suggestions will be appreciated by this novice. Thanks in advance!

 

[lark_tilly]

Take a look at the attached example.
It groups the report by usr_origin and puts a count by type in the group footer. Is that what you're looking for?
What is the DataType for the usr_elapsed time field? Is it Date/Time or Numeric?

 

[siouxprincess]

Design of report I'm trying to produce is this:

report header:
title of report

page header:
dept, id #, open date, closed date, origin, summary, desc, solution

detail:
dept group, id #, open date, closed date, usr_origin, summary, desc, solution
time to close: usr_elapsetime

page footer:
current date, page of page

report footer:
title "total summary"

total # of ids
total of elapsed time
usr_orgin "Internal Software Error": count how many were "Internal Software Error", then I need the total of usr_elapsedtime for "Internal Software Error"
usr_orgin "pc issue": count how many, total of usr_elapsedtime for "pc issue"

usr_elapsedtime is a numeric field

 

[siouxprincess]

=Sum(Iif([usr_Origin]="Internal Software Error"),1,0)

I've tried this code, but I'm getting an error: the expression you entered has a function containing the wrong number of arguements.

Any suggestions?

 

[siouxprincess]

=Sum(IIf([usr_Origin]="Internal Software Error",1,0))

Error message is gone. However it returns an answer of 0 instead of 1 (that I need as the answer).

If I use: =Count(IIf([usr_Origin]="Internal Software Error",1,0)) it counts all five origins (each one is different).

Any suggestions?

 

[Rich]

Check the spelling with that of the actual values in the field

 

[Brianwarnock]

Check the spelling with that of the actual values in the field

I'd guess that it should be Error not Errors.

Brian

 

[lark_tilly]

Use DCount against the query your report is built on.
=DCount("usr_origin","Your Query","usr_origin = 'Internal Software Errors'")

 

[siouxprincess]

=Sum(IIf([usr_Origin]='Internal Software Error',1,0)) answer to my first problem...

=Sum(IIf([usr_Origin]='Internal Software Error',[usr_elapsedtime],0)) answer to the second part of my problem...

FYI -- In the table it's "Internal Software Error". However, when it is viewed, it is "Internal Software Errors".

 

[Brianwarnock]

A "well done Brian" would have been nice but I will reconcile myself with the thought that 43 years in IT makes one a good guesser.

Brian

 

[Rich]

I did spot it first!

 

[Brianwarnock]

I did spot it first!

But I said exactly what it was,

Ok pistols at dawn.

brian

 

[siouxprincess]

Gratitude

Thanks lark, Rich and Brian for giving me suggestions.