Count distinct records on a report (1 Viewer)

spet

Registered User.
Local time
Yesterday, 18:25
Joined
Oct 5, 2018
Messages
22
Hello!


I have created a report that contains employee records. An employee can show up twice on the report if they have regular and overtime.


Example:
Emp No Hours Type

100 40 Reg
100 2 OT
102 40 Reg


At the bottom of the report, I would like to display how many employees are on the report, not the record count. Is there a way to do a Count(Distinct(EmpNo))???


Thank you in advance!
 

arnelgp

error reading drive A:
Local time
Today, 07:25
Joined
May 7, 2009
Messages
9,290
you can have an Unbound textbox at the bottom (Page footer or Report Footer).
add code to Load event of your report to count:
Code:
Private Sub Report_Load()
    Dim sSQL As String
    If InStr(Me.RecordSource, "SELECT") > 0 Then
        sSQL = "SELECT COUNT('1') FROM (SELECT DISTINCT T1.EMPNO FROM (" & Me.RecordSource & ") AS T1)"
    Else
        sSQL = "SELECT COUNT('1') FROM (SELECT DISTINCT T1.EMPNO FROM " & Me.RecordSource & " AS T1)"
    End If
    With CurrentDb.OpenRecordset(sSQL)
        Me.UnboundTextbox = .RecordCount
    End With
End Sub
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom