Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-06-2019, 05:45 AM   #1
spet
Newly Registered User
 
Join Date: Oct 2018
Posts: 17
Thanks: 6
Thanked 0 Times in 0 Posts
spet is on a distinguished road
Count distinct records on a report

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!

spet is offline   Reply With Quote
Old 09-06-2019, 06:27 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,401
Thanks: 68
Thanked 2,703 Times in 2,588 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Count distinct records on a report

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to pull all records if distinct count > 1 rreiling007 Queries 3 03-18-2014 03:50 PM
count distinct records nockam Queries 8 09-25-2011 06:57 PM
Distinct count of records Tep Modules & VBA 5 02-22-2011 03:52 AM
[SOLVED] Count Distinct in Report uv9z Reports 3 10-12-2005 11:36 AM
[SOLVED] Distinct Count in a Report Joffar Reports 0 04-08-2002 02:52 PM




All times are GMT -8. The time now is 06:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World