Counting total number of unique values in a report

[joe raposo]

Hi Everyone

Wonderining if you can help me with this issue. I have a database that I use
to record the quality of service my technical folks deliver to our customers.
Field managers travel with the techs to observe their activities on a typical
service call. Data is entered via a form that consists of a unique Employee
number,Travel Date and a list of 50 activities (fields) that I expect to see
and I rate them on. The ratining is "MR","RI" or "N/A".

I have created a field travel report that filters on the Employee number
field and travel date however I would like the report to count the number of
"MR" in all fields within that particular report for that particular travel
date.

I would appreciate any help you could provide.

Thanks

 

[John Spencer]

Because of your structure you have a problem that you may need to solve with a
VBA function.

Your table structure is wrong since you should have
-- Activities - one record for each type of activity
-- ActivityRatings - one record for each ServiceCall + ActivityType
-- Employees
-- ServiceCalls - ServiceCallID, EmployeeID, ServiceDate, CustomerID
That structure would make your analysis very easy.

With your current structure you probably need something like the following
UNTESTED function.
Assumptions:
--StrRating is a string,
--EmployeeNumber is a number field, and
--ServiceDate is a datetime field
--Only one record per employee per service date. If more you will need to
loop through all the records returned.

Public Function fCountRating(strRating, EmployeeNumber, ServiceDate)
'StrRating = the value you want to count
'EmployeeNumber = the unique employee identifier
'ServiceDate = the Date you are interested in
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim fldAny As DAO.Field
Dim lCount As Integer

'Build a string containing the activity fields
strSQL = "SELECT FieldA, FieldB FROM [YourTable] " & _
" WHERE EmployeeNumber = " & EmployeeNumber & _
" AND ServiceDate = " & Format(ServiceDate, "\#yyyy-mm-dd\#")
Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset(strSQL)

'Loop through all the fields and count those with the specified rating.
If rstAny.RecordCount > 0 Then
For Each fldAny In rstAny.Fields
If fldAny = strRating Then
lCount = lCount + 1
End If
Next fldAny

fCountRating = lCount
Else
fCountRating = Null
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County