asked on

How can I get a count of unique row values on a report?

In the database attached, the report, rpt_Training_Employees, lists employees and the training they've received. I need to list the number of unique employees in the top of the report, with, "There are x employees in this report". Since each employee is identifed by User_ID, I tried to total on that, but it gave me a count for each training the employee has and totalled those over the whole report. That would actually be the total training on the report. I don't need that. Instead, I need the total of unique employees listed. In this report, it would seem there are six, but one is listed twice for AM and PM shifts, so there should only be a count of five. I'd like this total in the report header.

I worked through this solution here: https://www.experts-exchange.com/questions/28607256/Count-number-of-unique-values-from-a-query.html?searchSuccess=true&searchTop10=true, but as the author pointed out, it just gives a total count of the values in the dataset of the query. It doesn't account for when a parameter has limited the number of users in the report. If you want to see how I would limit the report with parameters, see the form, frm_TrainingByEe.
SampleSkills.accdb

Graham Mandeno

Hi David

Is there any compelling reason for using embedded macros for your form events instead of VBA code?

I would use a VBA event procedure to construct the WhereCondition string and substitute the values of the form controls into this string instead of having your report refer to the form controls themselves. Something like this:

Private Sub cmd_Run_Report_Click()
  Dim sCondition As String
  If Not IsNull(Me.cbo_Shift) Then
    sCondition = "[Shift]=" & Me.cbo_Shift
  End If
  If Not IsNull(Me.cbo_Bank) Then
    If Len(sCondition) <> 0 Then sCondition = sCondition & " AND "
    sCondition = sCondition & "[Bank]=" & Me.cbo_Bank
  End If
  If Not IsNull(Me.cbo_Flow_ID) Then
    If Len(sCondition) <> 0 Then sCondition = sCondition & " AND "
    sCondition = sCondition & "[Flow_fk]=" & Me.cbo_Flow_ID
  End If
  If Not IsNull(Me.cbo_Queue_ID) Then
    If Len(sCondition) <> 0 Then sCondition = sCondition & " AND "
    sCondition = sCondition & "[Queue_fk]=" & Me.cbo_Queue_ID
  End If
  DoCmd.OpenReport "rpt_Training_Employees", acViewReport, , sCondition
End Sub

Open in new window

Then, add an unbound textbox named "txtEmployeeCount" to the report header of your report and add this VBA procedure to the report header's format event:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
  Dim sSQL As String
  Dim rs As DAO.Recordset
  Dim lEmployeeCount As Long
  Dim sCaption As String
  sSQL = "SELECT Count(*) from (SELECT DISTINCT User_ID FROM " & Me.RecordSource
  If Me.FilterOn And Len(Me.Filter) <> 0 Then
    sSQL = sSQL & " WHERE " & Me.Filter
  End If
  sSQL = sSQL & ")"
  Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenForwardOnly)
  lEmployeeCount = rs(0)
  rs.Close
  Set rs = Nothing
  sCaption = "There "
  If lEmployeeCount = 1 Then
    sCaption = sCaption & "is 1 employee"
  Else
    sCaption = sCaption & "are " & lEmployeeCount & " employees"
  End If
  sCaption = sCaption & " in this report."
  Me.txtEmployeeCount = sCaption
End Sub

Open in new window

This code counts the distinct User_IDs in the RecordSource of your report, using the same filter that was passed as the WhereCondition when you opened the report. It then constructs a string which it inserts into the unbound textbox. note that because of the vagaries of Report View, you will not be able to see this until you click "Print".

Good luck!
Graham mandeno [Access MVP since 1996]

PatHartman

You could break the report into a main report (employees) and sub report (training) which hopefully reflects the actual schema. That allows you to count the main report (employees) independently of what training they received.

David Bigelow

ASKER

Graham,

Thank you for the detailed work you posted!

I'm not sure if this was intended by the VBA code you posted, but after making it the Run Report On Click event code on the form, frm_TrainingByee, whenever I select a parameter in the form and run the report, I get a parameter dialog box. Then, if I input the parameter I had selected, the report runs fine. But I shouldn't have to do that; it should run based on the parameter selected in the combo box on the report. If I don't select any parameters, then the full report runs without any problem, i.e., without any parameter box pop ups.

Second, the VBA code you posted for the Report Header refers to a caption. Since the textbox is not filling with the count of the User IDs, when it is supposed to, I'm wondering if it's because there is no caption in a report header. At least, there isn't a property listed for one in the Report Header property list.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Graham Mandeno

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Bigelow

ASKER

I used a variation of Pat's suggestion that bypassed counting each UserID for every item of training and just counted the names that had training grouped under them.

I placed a label called txtUserCount in the Employee Name group header and set the Control Source to =1. With a Data Property of Running Sum as Over All.

In the footer of the report, I placed the label txtTotalUsers, with the Control Source as ="There are " & [txtUserCount] & " employees in this report."

Honorable mention to Graham Mandeno.