Find duplicates, count and then return unique id for duplicate count

Question

I am working with queries in MSAccess 2010.

I would like to find combinations of duplicates in multiple columns, count their frequency, give each count of resulting duplicates a unique ID, and group by one specific field. My table has over 90 fields and looks like this:

ID     Field1 Field2  Field3
-----------------------------
12554    0      1       0
12558    1      0       0
15488    1      0       0
11457    0      1       0
11554    1      1       0

I would like it to look like this:

RecID  Field1 Field2 Field3 FREQ  FREQID
-------------------------------------------------
11457    0      1       0    2     DU0001
11554    1      1       0    1     DU0002
12554    0      1       0    2     DU0003
12558    1      0       0    2     DU0004
15488    1      0       0    2     DU0004

I have returned successful results using SELECT, count(*), GROUP, and HAVING as far as the count and grouping go, but am stumped by how to add/code for an ID field that returns autogen results, and how to have each row on the table show up with the original RecID

Here's what's working so far, and am still stumped:

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Count(*) AS FREQ
FROM Table1
GROUP BY Table1.Field1, Table1.Field2, Table1.Field3
HAVING (Count(*) >= 1);

Because I'm new at this it would be ideal if it only involved a single query.

@mohan111 According to the tags, it's clearly SQL for Microsoft Access 2010, not T_SQL. — Gustav
Every inspiration counts, but neither CASE..END, OVER, PARTITION nor CAST are supported in Access SQL. — Gustav

Gustav Gustav · Accepted Answer · 2015-12-12T09:03:08

You can obtain the frequency ID this way:

FREQID: Format((Select Count(*) From YourTable As T Where T.ID < YourTable.ID),"\D\U0000")

Edit:

SELECT 
    Table1.ID AS RecID,
    Table1.Field1, 
    Table1.Field2, 
    Table1.Field3, 
    3 - Field1 - Field2 - Field3 AS FREQ,
    Format((Select Count(*) From Table1 As T Where T.ID < Table1.ID),"\D\U0000") AS FREQID
FROM 
    Table1
GROUP BY 
    Table1.ID,
    Table1.Field1, 
    Table1.Field2, 
    Table1.Field3
    3 - Field1 - Field2 - Field3
HAVING 
    3 - Field1 - Field2 - Field3 > 0

Find duplicates, count and then return unique id for duplicate count

1 Answers