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: http://www.experts-exchange.com/Database/MS_Access/Q_28607256.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
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamMandenoCommented:
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]
PatHartmanCommented:
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 BigelowStaff Operations SpecialistAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
Using a report/subreport requires no code.  All you need is a control in the main report footer

=Count(*)

Although you can put counts and other aggregations in section headers, they force Access to process the report's recordsource twice.  Once to aggregate and the second time to actually produce the report.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrahamMandenoCommented:
@Pat:  I thought of the subreport option first as well, but looking at David's report it is grouped by Shift > Supervisor > Bank > Employee, and it is possible for the same employee to work multiple shifts, so appear multiple times in the report.  Even by taking the training sessions out into a subreport, these employees would be counted multiple times.

@David:  What are the parameters being requested?  Are they like this: [Forms]![frm_TrainingByEe]![cbo_Shift]?

If so, then you are not executing the cmd_Run_Report_Click event procedure I gave you.  What this procedure specifically does is construct a WhereCondition string in which the references to the form controls (e.g. [Forms]![frm_TrainingByEe]![cbo_Shift]) are replaced by the selected values in those controls.

With your form in design view, right-click on the "Run Report" button, and select "Properties".  On the Event tab in the properties pane, you should see the "On Click" is [Event Procedure] (not [Embedded Macro]).  If this is wrong, then change it and then click on the button with the three dots.  It should take you to the code I posted.  If not, then repaste the code into your event procedure stub.

With regard to your caption question, I think the Caption you refer to is the string variable sCaption.  This is just a name - I could just as easily have named it FooBar.  The reason the count is not appearing in the box in Report view, is that Format events are not raised until the report is either printed or switched to Preview view.  (This is a bug, in my opinion)

However, I had a think about a workaround, and we can use the report's Open event which will fire no matter what the view.  Only problem is, you can't set the value of a textbox in the Open event.  However, you can set the Caption of a label.  So the solution is this:

Add a label to the report header named lblEmployeeCount.  Give it a placeholder caption so that it is visible - something like "This is a placeholder for the employee count".

Then add this (slightly modified) code as the event procedure for the report's open event:
Private Sub Report_Open(Cancel 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.lblEmployeeCount.Caption = sCaption
End Sub

Open in new window


Note that the property sheet for the Report should show [Event Procedure] for the On Open property.

Good luck!
Graham
David BigelowStaff Operations SpecialistAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.