Count Duplicate Records Once (Access 2000)

Topic

New Reply

I was wondering if there is a way to, in either a query or report, count the number of records in a column while only counting duplicate records once.

Reply | Quote

Replies

I’ve done this myself with 2 queries. just setting the second query to count.
see this post :-
“]http://www.wopr.com/cgi-bin/w3t/showthread…p;vc=1#Post5717%5D%5B/url%5D

Regards

Reply | Quote

(Edited by charlotte on 12-Dec-02 06:24. to Remark out notifications in error handler)

One way to do this w/o having to create multiple queries is create user-defined function to count unique records for specified field in a table. Example – add this function to a standard code module:

Public Function CountUniqueRecords(strTbl As String, strFld As String) As Long
On Error GoTo Err_Handler

Dim strMsg As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = “SELECT DISTINCT [” & strFld & “] FROM [” & strTbl & “];”
Set rst = db.OpenRecordset(strSQL)

With rst
If Not .EOF Then
.MoveLast
End If
CountUniqueRecords = .RecordCount
End With

Exit_Sub:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
‘ Beep
‘ MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub

End Function

Example of use in query (using Order Details table in Northwind.mdb):

SELECT Count([Order Details].ProductID) AS [Count All], CountUniqueRecords(“Order Details”,”ProductID”) AS [Count Unique]
FROM [Order Details];

This totals query returns 1 row, showing total count of ProductID field in 1st column and count of unique ProductID’s in 2nd column. (In my copy of Northwind these totals were 2155 and 77, respectively.) Note if function used in a select query, it’ll be executed for each row returned by query.

Example of use in report: Add an unbound textbox to report and set its ControlSource to following expression (using same example):

=CountUniqueRecords(“Order Details”,”ProductID”)

When report runs textbox will display “77” (in this case). You would replace table and field names with actual table & field names in your database. If you want to count unique records with Where criteria applied the CountUniqueRecords function would have to be modified accordingly. If using A2K or later ensure a reference to Microsoft DAO 3.6 Object Library has been set.

HTH

Reply | Quote

Great function, Mark. Thanks!

Here is a version that takes an optional Where condition:

‘ *** Optional argument strCnd added ***
Public Function CountUniqueRecords(strTbl As String, strFld As String, _
Optional strCnd As String) As Long

On Error GoTo Err_Handler

Dim strMsg As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = “SELECT DISTINCT [” & strFld & “] FROM [” & strTbl & “]”
‘ *** Modification starts ***
If strCnd “” Then
strSQL = strSQL & ” WHERE ” & strCnd
End If
‘ *** Modification ends ***
Set rst = db.OpenRecordset(strSQL)

With rst
If Not .EOF Then
.MoveLast
End If
CountUniqueRecords = .RecordCount
End With

Exit_Sub:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Err_Handler:
Beep
‘ See remark about displaying a message box below
‘ strMsg = “Error No ” & Err.Number & “: ” & Err.Description
‘ MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub
End Function

Note: I am not sure that having a MsgBox in the error handler is a good idea here. If an error occurs in the query you give as example, you’ll probably have to click OK 2155 times…

MarkD’s examples will work with the modified function; if the strCnd argument is omitted, the function acts as Mark’s original function.

Example of use with Where condition in a query based on the Products table in the Northwind database:

SELECT Products.CategoryID, CountUniqueRecords(“Products”,”SupplierID”,”CategoryID=” & [CategoryID]) AS CountOfSuppliers
FROM Products
GROUP BY Products.CategoryID;

Example of use with Where condition in a report: create a report based on the Products table, grouped by CategoryID. Place an unbound text box in the group footer and set its Control Source to

=CountUniqueRecords(“Products”,”SupplierID”,”CategoryID=” & [CategoryID])

Reply | Quote

You’re correct about the MsgBox in error handler – not a good idea if used in select query that may list many records. I didn’t think to modify generic error handler I use for new subs. Function is really intended for a single-row totals query as shown in example posted. Having many times wound up clicking “OK” over & over again when function in query generated error I should’ve caught this!

Reply | Quote