Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Duplicate Records Once (Access 2000)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Duplicate Records Once (Access 2000)

    I've done this myself with 2 queries. just setting the second query to count.
    see this post :-
    <font color=blue> http://www.wopr.com/cgi-bin/w3t/showthread...p;vc=1#Post5717] </font color=blue>

    Regards

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Count Duplicate Records Once (Access 2000)

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Duplicate Records Once (Access 2000)

    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","Catego ryID=" & [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","Categ oryID=" & [CategoryID])

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Count Duplicate Records Once (Access 2000)

    <P ID="edit" class=small>(Edited by charlotte on 12-Dec-02 06:24. to Remark out notifications in error handler)</P>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 <!t>[" & strFld & "]<!/t> FROM <!t>[" & strTbl & "]<!/t>;"
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •