CODEWITHSUNDEEP
sqlms-accessvb6
Sancho Almeda
Sancho Almeda

Reputation: 151

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

I have an access table with 11 columns.

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?

Upvotes: 0

Views: 7451

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8404

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.

Upvotes: 2

Related Questions