One report, need to count different records based on criteria

[TammyT]

Okay, the title is crazy - couldn't figure any better way to say it! Here goes:

The table stores data on surveys attempted/done on our employees. There is a record for each survey attempt, & it is either completed because there are scores, or it is marked as either Unavailable or Refused (no scores would be entered). ALL records do have a date, employee name, & ticket #.

I'm trying to get totals that should be easy! I want Access to count the # of records where Unavailable was checked, the # of records where Refused was checked, all records for the time period selected (got that one!), & then subtract the Unavailables & Refused from the Total to give me a count of Completed surveys.

Is this simple cancatination, or do I need to use code On Open?

Thanks!

 

[earthandfire]

Wouldn't it be easier to do this in a query which could be your data source for the report?

 

[TammyT]

I am using a query - but that query is "generic" - I use it for a few reports that pull from the same data, but give different info.

Besides - I don't know how to get a query to total! (Been a loooooong time since training, & don't use Access often enough - only when boss needs changes!).

Tell me how - I can always do a special query for this report if needed.

Thanks!

 

[Stevehewitt]

Hey,

Had something very similar recently. I had all of the records listed in the detail part of the report - but hidden and very, very small. Then in the footers I had:

=Abs(Sum([PackOut]=-1))

This will count the number of records in the report that have a check in the "PackOut" field.


The rest is just simple math sums done in hidden text boxes which can then lead you to the totals you need.

Hope this helps - or am I barking up the wrong tree?!

Good luck,


Steve.

 

[TammyT]

Actually, that looks good - can you give me more details? Coding is not my strength (maybe someday I'll get training!).

Check me here: once I get the Abs(Sum) for the 2 types, then I need to get that # into the Date Footer - just put an unbound field in, & make it =[Abs(Sum)] field? That seems obvious - almost 2 obvious!!!

Thanks.

 

[dhookom]

Steve was showing how to use an expression that returns either True/-1 or False/0. The Abs() function converts True expressions to 1 and False expressions stay 0.

You can Sum() the Abs([Your Expression]) to get a count of the number of records where [Your Expression] is True.

Assuming you have an employee table with gender and HireDate. You want to display the number of females that have worked for your company since before 1/1/2000.
=Sum( Abs( [Gender]="F" And [HireDate]<#1/1/2000#) )
This expression will work in any report section other than PAGE HEADERS and PAGE FOOTERS.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TammyT]

Okay, I've got my total calls & my counts of just the Unavaiables & Refused. I think it's just too late on a Friday!! Why can't I get the Total Calls - (Unavailable+Refused) ??

I thought I only needed to do:

=Sum([fldTotalCalls]-(Sum([fldUnavailable]+[fldRefused])))

However, then I get the msg: Cannot have aggregate function in expression Sum([fldTotalCalls]-(Sum([fldUnavailable]+[fldRefused])))

Help?

 

[TammyT]

Oh, nevermind! I just took out Sum in both spots & it works fine. Told you it was too late on a Friday!!!