Counting "Employee" and "Public"

[sTrongFuse]

This should be (and probably is) fairly simple, but for some reason my brain has gone into meltdown and I can't figure it out.

I have a fairly simple database (A2007) for recording accidents.

I have built a report that, for a given date range, lists all the incidents within that time span.

In the footer, I have the expression =Count(*) which tells me, numerically, how many incidents there are. Last month there were 26.

So far, so good, apart maybe, for the 26 individuals involved in the accidents.

One of the fields in the report is [Employee] will return either "Employee" or "Public" depending on which value was chosen in the form which populates the table, the feeds the query, that the report is built from. The original control in the table is a look-up with 2 values: you guessed it: Employee and Public.

What I am trying to do is put an expression in my report that counts the number of times "Employee" has been returned and another expression that counts the number of times "Public" is returned.

In the footer of my report I have the following expressions:

=Count(*) - Returns the value 26 as expected.
=Count([Employee]="Employee") - Returns the value 26. Correct answer is 19
=Count([Employee]="Public") - Returns the value 26. Correct answer is 7

What am I doing wrong and what do I need to do to get the answers I want?

Thanks in advance,

T

 

[pr2-eugin]

Hello sTrongFuse, could you not use DCount? Which will take the Criteria as you want it to be..

 

[pbaldy]

Try

=Sum(IIf([Employee]="Employee", 1, 0))

 

[sTrongFuse]

Try

=Sum(IIf([Employee]="Employee", 1, 0))

You've come to my rescue yet again. Many thanks.

 

[sTrongFuse]

Hello sTrongFuse, could you not use DCount? Which will take the Criteria as you want it to be..

I possibly could, but pbaldy's one was easier as I'm familiar with the syntax of IIF but I've never used a DCount.

Cheers anyway,

T

 

[pbaldy]

Happy to help!