Identify a 3-Column Pk Duplicate in Vba Access

Loops through Access column and find duplicates

Use the RecordsetClone:

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

With rst
    Do Until .EOF
        If !Name.Value = Me!Name.Value Then
            If !ID.Value <> Me!ID.Value Then
                .Edit
                    !Duplicate.Value = True
                .Update
            End If
        End If
        .MoveNext
    Loop
End With

Set rst = Nothing

How to create a calculated column in access 2013 to detect duplicates

First and foremost, understand MS Access' Expression Builder is a convenience tool to build an SQL expression. Everything in Query Design ultimately is to build an SQL query. For this reason, you have to use a set-based mentality to see data in whole sets of related tables and not cell-by-cell mindset.

Specifically, to achieve:

putting 1 only on the first appearance of that location, putting 0 on next appearances

Consider a whole set-based approach by joining on a separate, aggregate query to identify the first value of your needed grouping, then calculate needed IIF expression. Below assumes you have an autonumber or primary key field in table (a standard in relational databases):

Aggregate Query (save as a separate query, adjust columns as needed)

SELECT ColumnD, MIN(AutoNumberID) As MinID
FROM myTable
GROUP BY ColumnD

Final Query (join to original table and build final IIF expression)

SELECT m.*, IIF(agg.MinID = AutoNumberID, 1, 0) As Dup_Indicator
FROM myTable m
INNER JOIN myAggregateQuery agg
   ON m.[ColumnD] = agg.ColumnD

---

To demonstrate with random data:

Original

| ID | GROUP  | INT | NUM          | CHAR | BOOL  | DATE       |
|----|--------|-----|--------------|------|-------|------------|
| 1  | r      | 9   | 1.424490258  | B6z  | TRUE  | 7/4/1994   |
| 2  | stata  | 10  | 2.591235683  | h7J  | FALSE | 10/5/1971  |
| 3  | spss   | 6   | 0.560461966  | Hrn  | TRUE  | 11/27/1990 |
| 4  | stata  | 10  | -1.499272175 | eXL  | FALSE | 4/17/2010  |
| 5  | stata  | 15  | 1.470269177  | Vas  | TRUE  | 6/13/2010  |
| 6  | r      | 14  | -0.072238898 | puP  | TRUE  | 4/1/1994   |
| 7  | julia  | 2   | -1.370405263 | S2l  | FALSE | 12/11/1999 |
| 8  | spss   | 6   | -0.153684675 | mAw  | FALSE | 7/28/1977  |
| 9  | spss   | 10  | -0.861482674 | cxC  | FALSE | 7/17/1994  |
| 10 | spss   | 2   | -0.817222582 | GRn  | FALSE | 10/19/2012 |
| 11 | stata  | 2   | 0.949287754  | xgc  | TRUE  | 1/18/2003  |
| 12 | stata  | 5   | -1.580841322 | Y1D  | TRUE  | 6/3/2011   |
| 13 | r      | 14  | -1.671303816 | JCP  | FALSE | 5/15/1981  |
| 14 | r      | 7   | 0.904181025  | Rct  | TRUE  | 7/24/1977  |
| 15 | stata  | 10  | -1.198211174 | qJY  | FALSE | 5/6/1982   |
| 16 | julia  | 10  | -0.265808162 | 10s  | FALSE | 3/18/1975  |
| 17 | r      | 13  | -0.264955027 | 8Md  | TRUE  | 6/11/1974  |
| 18 | r      | 4   | 0.518302149  | 4KW  | FALSE | 9/12/1980  |
| 19 | r      | 5   | -0.053620183 | 8An  | FALSE | 4/17/2004  |
| 20 | r      | 14  | -0.359197116 | F8Q  | TRUE  | 6/14/2005  |
| 21 | spss   | 11  | -2.211875193 | AgS  | TRUE  | 4/11/1973  |
| 22 | stata  | 4   | -1.718749471 | Zqr  | FALSE | 2/20/1999  |
| 23 | python | 10  | 1.207878576  | tcC  | FALSE | 4/18/2008  |
| 24 | stata  | 11  | 0.548902226  | PFJ  | TRUE  | 9/20/1994  |
| 25 | stata  | 6   | 1.479125922  | 7a7  | FALSE | 3/2/1989   |
| 26 | python | 10  | -0.437245299 | r32  | TRUE  | 6/7/1997   |
| 27 | sas    | 14  | 0.404746106  | 6NJ  | TRUE  | 9/23/2013  |
| 28 | stata  | 8   | 2.206741458  | Ive  | TRUE  | 5/26/2008  |
| 29 | spss   | 12  | -0.470694096 | dPS  | TRUE  | 5/4/1983   |
| 30 | sas    | 15  | -0.57169507  | yle  | TRUE  | 6/20/1979  |

SQL (uses aggregate in subquery but can be a stored query)

SELECT r.*, IIF(sub.MinID = r.ID,1, 0) AS Dup
FROM Random_Data r
LEFT JOIN 
   (
      SELECT r.GROUP, MIN(r.ID) As MinID
      FROM Random_Data r
      GROUP BY r.Group
   )  sub

ON r.[Group] = sub.[GROUP] 

Output (notice the first GROUP value is tagged 1, all else 0)

| ID | GROUP  | INT | NUM          | CHAR | BOOL  | DATE       | Dup |
|----|--------|-----|--------------|------|-------|------------|-----|
| 1  | r      | 9   | 1.424490258  | B6z  | TRUE  | 7/4/1994   | 1   |
| 2  | stata  | 10  | 2.591235683  | h7J  | FALSE | 10/5/1971  | 1   |
| 3  | spss   | 6   | 0.560461966  | Hrn  | TRUE  | 11/27/1990 | 1   |
| 4  | stata  | 10  | -1.499272175 | eXL  | FALSE | 4/17/2010  | 0   |
| 5  | stata  | 15  | 1.470269177  | Vas  | TRUE  | 6/13/2010  | 0   |
| 6  | r      | 14  | -0.072238898 | puP  | TRUE  | 4/1/1994   | 0   |
| 7  | julia  | 2   | -1.370405263 | S2l  | FALSE | 12/11/1999 | 1   |
| 8  | spss   | 6   | -0.153684675 | mAw  | FALSE | 7/28/1977  | 0   |
| 9  | spss   | 10  | -0.861482674 | cxC  | FALSE | 7/17/1994  | 0   |
| 10 | spss   | 2   | -0.817222582 | GRn  | FALSE | 10/19/2012 | 0   |
| 11 | stata  | 2   | 0.949287754  | xgc  | TRUE  | 1/18/2003  | 0   |
| 12 | stata  | 5   | -1.580841322 | Y1D  | TRUE  | 6/3/2011   | 0   |
| 13 | r      | 14  | -1.671303816 | JCP  | FALSE | 5/15/1981  | 0   |
| 14 | r      | 7   | 0.904181025  | Rct  | TRUE  | 7/24/1977  | 0   |
| 15 | stata  | 10  | -1.198211174 | qJY  | FALSE | 5/6/1982   | 0   |
| 16 | julia  | 10  | -0.265808162 | 10s  | FALSE | 3/18/1975  | 0   |
| 17 | r      | 13  | -0.264955027 | 8Md  | TRUE  | 6/11/1974  | 0   |
| 18 | r      | 4   | 0.518302149  | 4KW  | FALSE | 9/12/1980  | 0   |
| 19 | r      | 5   | -0.053620183 | 8An  | FALSE | 4/17/2004  | 0   |
| 20 | r      | 14  | -0.359197116 | F8Q  | TRUE  | 6/14/2005  | 0   |
| 21 | spss   | 11  | -2.211875193 | AgS  | TRUE  | 4/11/1973  | 0   |
| 22 | stata  | 4   | -1.718749471 | Zqr  | FALSE | 2/20/1999  | 0   |
| 23 | python | 10  | 1.207878576  | tcC  | FALSE | 4/18/2008  | 1   |
| 24 | stata  | 11  | 0.548902226  | PFJ  | TRUE  | 9/20/1994  | 0   |
| 25 | stata  | 6   | 1.479125922  | 7a7  | FALSE | 3/2/1989   | 0   |
| 26 | python | 10  | -0.437245299 | r32  | TRUE  | 6/7/1997   | 0   |
| 27 | sas    | 14  | 0.404746106  | 6NJ  | TRUE  | 9/23/2013  | 1   |
| 28 | stata  | 8   | 2.206741458  | Ive  | TRUE  | 5/26/2008  | 0   |
| 29 | spss   | 12  | -0.470694096 | dPS  | TRUE  | 5/4/1983   | 0   |
| 30 | sas    | 15  | -0.57169507  | yle  | TRUE  | 6/20/1979  | 0   |

Access (VBA) - Append Records Not Already In Table

Use constraints to enforce this behavior, in this case a (composite) primary key: https://support.office.com/en-us/article/Add-or-change-a-table-s-primary-key-in-Access-07b4a84b-0063-4d56-8b00-65f2975e4379

This will make sure, that you can't insert duplicate values into your table, which means that you won't have to delete the duplicates later on.

Define the primary key over all the columns that make a dataset unique.

Before adding a pk or constraint make sure to clean up your data though in order to remove all duplicate rows. The easiest way would probably to create a new table and fill it by using a SELECT DISTINCT .... from your current table.

SQL NOT EXISTS - inserting without duplicates

Yesssss, I have finally managed It to work, It was a whole day nightmare. Here It is:

Private Sub Command4_Click()

Dim SQL As String

SQL = "INSERT INTO COMPANY(CompanyNumber, CompanyName)" & _
            " SELECT DISTINCT Number, Company" & _
            " FROM NEW" & _
            " WHERE NOT EXISTS " & _
            "(SELECT * FROM COMPANY WHERE" & _
            " (NEW.Number=COMPANY.CompanyNumber AND NEW.Company=COMPANY.CompanyName)" 

    DoCmd.RunSQL SQL

End Sub

@DylanSue thanks, all to all others, you helped me a lot. Looks like MS-access is quite strange when doing duplicates. I added DISTINCT because second table has many same entries. However, If I execute same code twice, It imports again 2 rows - one of them is empty on both columns and one only in one column. How can I avoid of blank entries for both columns ?? (If one column is blank, then It should import also).

Count number of entries where Col1 has multiple entries with different data in Col2

I think the following should perform as you require:

select t1.* from YourTable t1
where exists (select 1 from YourTable t2 where t1.col1 = t2.col1 and t1.col2 <> t2.col2)

Change both occurrences of YourTable to suit your table name.

For every record, this uses a correlated subquery to test whether another record exists in the dataset with the same col1 value as the current record, but a different col2 value, thus meeting your criteria.

The use of select 1 is purely for optimisation: we don't care what data is returned by the subquery, only that it has records.

Access not throwing error if duplicate records are inserted into table

Instead of the convenience method, TransferSpreadsheet, consider using a direct SQL query to Excel workbook (which is allowable in Access SQL) to capture the exception error as shown below. Because MS Access is both a GUI application and database engine, you would need to enable two different references. Adjust below SQL statement with actual columns, names, and paths.

Public Sub CaptureExceptions()
On Error GoTo ErrHandle
    Dim strSQL As String
    Dim db As DAO.Database                   ' ENABLE Microsoft Office x.x Access database engine object library
    Dim appAccess As Access.Application      ' ENABLE Microsoft Access x.x Object Library

    Set appAccess = CreateObject("Access.Application")

    appAccess.OpenCurrentDatabase "C:\Path\To\Access\Database.accdb"

    strSQL = "INSERT INTO my_db_table_name (Col1, Col2, Col3, ...)" _
              & " SELECT Col1, Col2, Col3, ..." _
              & " FROM [Excel 12.0 Xml; HDR = Yes;Database=C:\Path\To\Excel\Workbook.xlsx].[SheetName$];"

    Set db = appAccess.CurrentDb()
    db.Execute strSQL, dbFailOnError

    appAccess.CloseCurrentDatabase

ExitHandle:
    Set db = Nothing: Set appAccess = Nothing
    Exit Sub

ErrHandle:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandle
End Sub

---

Related Topics