Attempting to Count Distinct Values in Query Results (1 Viewer)

sherlocked · Jan 18, 2018

Hello Experts!

I am attempting to count the number of distinct employee ID values in my query results, with little luck. Trying to design a pop-up window that shows the total. Below is my code. It's counting the values, but not correctly - it's not eliminating duplicates.

The SQL behind my query is also below, in case that helps. Any ideas what I'm doing wrong?

Code:

Private Sub btnTEST_Click()
Dim Count As Integer

Count = DCount("[Employee Number]", "qryOTEmployeeCount")

MsgBox "A total of " & Count & " employees worked OT between these dates.", vbOKOnly + vbInformation

End Sub

Code:

SELECT OvertimeWorked.[Employee Number]
FROM OvertimeWorked
GROUP BY OvertimeWorked.[Employee Number], OvertimeWorked.[Time OUT], OvertimeWorked.VerifiedBy, OvertimeWorked.VerifiedDate, OvertimeWorked.NotWorked
HAVING (((OvertimeWorked.[Time OUT]) Between [forms]![frmSupDash2]![txtStartDate] And [forms]![frmSupDash2]![txtEndDate]) AND ((OvertimeWorked.VerifiedBy) Is Not Null) AND ((OvertimeWorked.VerifiedDate) Is Not Null) AND ((OvertimeWorked.NotWorked)=No));

plog · Jan 18, 2018

You can't get there with that query alone. Have you opened that query up and looked at the results? Should there be duplicates in there?

When you use a GROUP BY, the query makes each record returned "unique" by the permutations you have in the GROUP BY. If you only want it unique by one field, only put that one field in the GROUP BY.

Further (but unrelated to your issue), HAVING is criteria on the aggregate (SUM, COUNT, MAX, etc.). If you want criteria on individual records you shoudl use a WHERE. So, your HAVING should be a WHERE.

sherlocked · Jan 18, 2018

Thanks for your quick response! I made a few adjustments to my query and got the results I was going for

Below is what I went with, for those future supplicants like myself who may review this thread. Many thanks!

Code:

SELECT OvertimeWorked.[Employee Number]
FROM OvertimeWorked
WHERE (((OvertimeWorked.[Time OUT]) Between [forms]![frmSupDash2]![txtStartDate] And [forms]![frmSupDash2]![txtEndDate]) AND ((OvertimeWorked.VerifiedBy) Is Not Null) AND ((OvertimeWorked.VerifiedDate) Is Not Null) AND ((OvertimeWorked.NotWorked)=No))
GROUP BY OvertimeWorked.[Employee Number];

Attempting to Count Distinct Values in Query Results (1 Viewer)

sherlocked

Registered User.

plog

Banishment Pending

sherlocked

Registered User.

Similar threads

Users who are viewing this thread