Sql – Finding Duplicates in Access with more than 2 columns as basis and results

ms-accesssqlvb6

I have an access table with 11 columns.

PeriodYear
PeriodCycle
PeriodZone
PHSRCode
LastName
FirstName
PRC
Specialty
HCPType
Class
Institution

I want to find duplicate entries for it using Lastname, Firstname, and Institution as the basis where to find the duplicates.

Example Table:

    PeriodYear  PeriodCycle  PeriodZone  PHSRCode  Lastname  Firstname  PRC  Specialty  HCPType  Class  Institution

    2013        1            1           SF1-01    Almeda    Sancho     111  GP         CON      A      PGH
    2013        1            2           SF1-01    Almeda    Sancho     111  GP         CON      A      LPDH
    2013        1            3           SF1-01    Almeda    Sancho     222  GP         CON      A      PGH

The result should be:

    PeriodYear  PeriodCycle  PeriodZone  PHSRCode  Lastname  Firstname  PRC  Specialty  HCPType  Class  Institution

    2013        1            1           SF1-01    Almeda    Sancho     111  GP         CON      A      PGH
    2013        1            3           SF1-01    Almeda    Sancho     222  GP         CON      A      PGH

The query will be based on the fields PeriodYear, PeriodCycle, and PHSRCode, which will come from variables.

Is this possible?

Best Answer

First create a "pointer" query that determines all records that have dupes:

SELECT LastName, FirstName, Institution, Sum(1) as CNT
FROM MyTableName
GROUP BY LastName, FirstName, Institution
HAVING (Sum(1) > 1)

Call that Query1 or something.

Then LEFT JOIN Query1 to MyTableName on those fields, and use a WHERE clause to only keep the data you want based on your variables:

SELECT B.*
FROM Query1 A
LEFT JOIN MyTableName B
ON A.LastName = B.LastName 
AND A.FirstName = B.FirstName
AND A.Institution = B.Institution
WHERE B.PeriodYear = Forms!frmMyForm!txtPeriodYear
AND B.PeriodCycle = Forms!frmMyForm!txtPeriodCycle
AND B.PHSRCode = Forms!frmMyForm!txtPHSRCode

You will obviously have to do some tweaking, but this should give you the right idea.

Related Solutions

Sql – Finding duplicate values in a SQL table

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Simply group on both of the columns.

Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of "functional dependency":

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

Support is not consistent:

Recent PostgreSQL supports it.
SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.
MySQL is unpredictable and you need sql_mode=only_full_group_by:
- GROUP BY lname ORDER BY showing wrong results;
- Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer).
Oracle isn't mainstream enough (warning: humour, I don't know about Oracle).

Sql – Removing duplicates from SQL Join

You don't want to do a join per se, you're merely testing for existence/set inclusion.

I don't know what current flavor of SQL you're coding in, but this should work.

SELECT MAX(recid), firstname, lastname 
FROM table2 T2
WHERE EXISTS (SELECT * FROM table1 WHERE firstname = T2.firstame AND lastname = T2.lastname)
GROUP BY lastname, firstname

If you want to implement as a join, leaving the code largely the same:

i.e.

SELECT max(t2.recid), t2.firstame, t2.lastname 
FROM Table2 T2 
INNER JOIN Table1 T1 
    ON T2.firstname = t1.firstname and t2.lastname = t1.lastname
GROUP BY t2.firstname, t2.lastname

Depending on the DBMS, an inner join may be implemented differently to an Exists (semi-join vs join) but the optimizer can sometimes figure it out anyway and chose the correct operator regardless of which way you write it.

Best Answer

Related Solutions

Sql – Finding duplicate values in a SQL table

Sql – Removing duplicates from SQL Join

Related Topic