sqlexcelms-accessdrop-duplicates

Design MS Access SQL Query to mimic 'Remove Duplicate' Excel function

In MS Access 2016 I want to run a query that is synonymous with the Excel 'Remove Duplicates' function.

The excel query will remove duplicates and always leave one record. For example, if we have 4 records where Specimen = BB000127 AND ADDON = NO (last column below) and we specify the Remove Duplicate query for Specimen and ADDON columns ,

enter image description here

the excel Remove Duplicate Function always returns at least one record: enter image description here

when I run SQL query below in MS Access 2016 it remove's ALL records where duplicates are found:

  SELECT 
    ReportX.[Specimen], ReportX.[ADDON], ReportX.[Dept], ReportX.[Location], ReportX.[MRN], 
    ReportX.[Solcited_Status], ReportX.[FillerOrderNumber], ReportX.[PlaceGroupNumber], ReportX.[OrderType], 
    ReportX.[Investigation], ReportX.[Rejected], ReportX.[Mis_Un_labelled], ReportX.[No_samp_recd], 
    ReportX.[Amendedreport], ReportX.[OrderedBy], ReportX.[RespClinican], ReportX.[Collecteddate], 
    ReportX.[CollecteddateTime], ReportX.[messagedat]
FROM 
    ReportX
WHERE 
    ReportX.[Specimen] NOT IN (
        SELECT 
            [Specimen] 
        FROM 
            [ReportX] AS Tmp 
        GROUP BY 
            [Specimen], [ADDON] 
        HAVING 
            Count(*) >= 1  
            AND 
            [ADDON] = [ReportX].[ADDON]
    )
ORDER BY 
    ReportX.[Specimen], ReportX.[ADDON];

How can I modify the above query to return at least one record where a duplicate/duplicates exist?

Ive tried using FIRTS to select the first record where duplicates occur in a Sub Query, and then a JOIN to the main table form Sub Query, but duplicates are still been returned, any advice appreciated:

SELECT 
    SubQ.[Specimen], 
    SubQ.[ADDON], 
    ReportX.[Dept], 
    ReportX.[Location], 
    ReportX.[MRN], 
    ReportX.[Solcited_Status], 
    ReportX.[FillerOrderNumber], 
    ReportX.[PlaceGroupNumber], 
    ReportX.[OrderType], 
    ReportX.[Investigation], 
    ReportX.[Rejected], 
    ReportX.[Mis_Un_labelled], 
    ReportX.[No_samp_recd], 
    ReportX.[Amendedreport], 
    ReportX.[OrderedBy], 
    ReportX.[RespClinican], 
    ReportX.[Collecteddate], 
    ReportX.[CollecteddateTime], 
    ReportX.[messagedat]
FROM 
    (SELECT 
        FIRST(ReportX.[Specimen]) AS [Specimen], 
        FIRST(ReportX.[ADDON]) AS [ADDON]
    FROM 
        ReportX
    GROUP BY 
        ReportX.[Specimen], ReportX.[ADDON]) AS SubQ
INNER JOIN 
    ReportX ON SubQ.[Specimen] = ReportX.[Specimen] AND SubQ.[ADDON] = ReportX.[ADDON];

UPDATE: Providing sample data with duplicates in 'Specimen' and 'ADDON' records as requested, doesn't appear to be an option to attach a txt file so its below, exported using Windows encoding.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|     Specimen      |       Dept        |     Location      |        MRN        |  Solcited_Status  | FillerOrderNumber | PlaceGroupNumber  |     OrderType     |   Investigation   |       ADDON       |     Rejected      |  Mis_Un_labelled  |   No_samp_recd    |   Amendedreport   |     OrderedBy     |   RespClinican    |    hl7message     |   Collecteddate   | CollecteddateTime |    messagedat     |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| HH014977V         | Haematology       | ED                |           1184842 | 000376998^ILAB    | |20240503HH014977 | 000376998^ILAB    | SOLICTED          | FBCD^FBC with     | NO                | N/A               | N/A               | N/A               | NA                |                   | DUMMY    | MSH|^~\&|ILAB|LAB |       03/05/2024  |      202405031917 |       03/05/2024  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| HH014978B         | Haematology       | ED                |           1145832 | 000376985^ILAB    | |20240503HH014978 | 000376985^ILAB    | SOLICTED          | FBCD^FBC with     | NO                | N/A               | N/A               | N/A               | NA                |                   | DUMMY  | MSH|^~\&|ILAB|LAB |       03/05/2024  |      202405031926 |       03/05/2024  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| HH014976J         | Haematology       | ED                |            586273 | 000377002^ILAB    | |20240503HH014976 | 000377002^ILAB    | SOLICTED          | FBCD^FBC with     | NO                | N/A               | N/A               | N/A               | NA                |                   | DUMMY  | MSH|^~\&|ILAB|LAB |       03/05/2024  |      202405031931 |       03/05/2024  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| BB047821M         | Biochemistry      | ED                |            505432 | 000377000^ILAB    | |20240503BB047821 | 000377000^ILAB    | SOLICTED          | CO2^Total CO2^B   | NO                | N/A               | N/A               | N/A               | NA                |                   | DUMMY  | MSH|^~\&|ILAB|LAB |       03/05/2024  |      202405031921 |       03/05/2024  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| BB047821M         | Biochemistry      | ED                |            505432 | 000377000^ILAB    | |20240503BB047821 | 000377000^ILAB    | SOLICTED          | CRP^C Reactive    | NO                | N/A               | N/A               | N/A               | NA                |                   | DUMMY  | MSH|^~\&|ILAB|LAB |       03/05/2024  |      202405031921 |       03/05/2024  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| BB047821M         | Biochemistry      | ED                |            505432 | 000377000^ILAB    | |20240503BB047821 | 000377000^ILAB    | SOLICTED          | UE^Urea and       | NO                | N/A               | N/A               | N/A               | NA                |                   | DUMMY  | MSH|^~\&|ILAB|LAB |       03/05/2024  |      202405031921 |       03/05/2024  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Solution

  • As I see it, you don't need a subquery.

    Just a GROUP BY the columns that define unique records, and an aggregate function (First) for all other columns.

    SELECT 
    Specimen, 
    ADDON, 
    First(Dept), 
    First(Location),
    First(MRN), 
    First(Solcited_Status),
    ... 
FROM ReportX 
GROUP BY Specimen, ADDON

    This will return one record per Specimen + ADDON.