Search code examples
ms-accessduplicatescountif

Countif in access - finding duplicates and indexing them

When working in Excel I usually use Countif to find and index my duplicates.

Counting: =Countif(A:A,A1)

Index: =Countif($A$1:A2,A2)

And the result would look like this

enter image description here

I am having a huge access table that I would like to do the same in. Within the table iteself. Are there any good way to do the same operations in Access? I have seen that you can use some other operations, but I have an infinite numbers that the first column can be, and I would therefore like to be able to look at each number and from there do the counting and indexing.

Solution

  • For this requirement you need a column that defines the order of the rows, like an auto incremented id.

    If there is one, you can do it with 2 correlated subqueries:

    SELECT t.[NUMBER],
       (SELECT COUNT(*) FROM tablename AS tt WHERE tt.[NUMBER] = t.[NUMBER]) AS Duplicates,
       (SELECT COUNT(*) FROM tablename AS tt WHERE tt.[NUMBER] = t.[NUMBER] AND tt.ID <= t.ID) AS [Index]
FROM tablename AS t

    Or with a self join and aggregation:

    SELECT t1.[NUMBER],
       COUNT(*) AS Duplicates,
       COUNT(IIF(t2.id <= t1.id, 1, NULL)) AS [Index]
FROM tablename AS t1 INNER JOIN tablename AS t2
ON t2.[NUMBER] = t1.[NUMBER] 
GROUP BY t1.id, t1.[NUMBER]