Access: Count the First Instance of Each Unique Value in a Column

Question

I am trying to return a 1 for the first instance of each unique value in a column, and return a 0 for each repeating value after the first unique instance.

In Excel I've used the below formula but on a larger sheet it becomes unstable.

=IF(COUNTIF($A$2:A2,A2)=1,1,0)

ID    Unique
ABC1       1
ABC1       0
ABC1       0
ABC2       1
ABC3       1
ABC3       0
ABC4       1

MS Access works with unordered datasets, therefore you'll first need to define a sort order so that you can unambiguously define what constitutes the "first instance". Does your table have an incrementing primary key by which it could be sorted, for example? — Lee Mac, Jan 13 '20 at 19:00
Does your table have `ID` and a separate primary key column? — Bohemian, Jan 13 '20 at 23:48

June7 · Answer 1 · 2020-01-13T19:33:06.710

Here is one way:

SELECT IDPK, ID, DCount("*","TableName","ID='" & [ID] & "' AND IDPK<" & [IDPK])+1 AS U, 
       IIf([U]=1,1,0) AS [Unique]
FROM TableName
ORDER BY IDPK;

Be aware that domain aggregate functions can cause slow performance in large datasets. However, same can be said of nested subquery to accomplish same result. More info How to show row number in Access query like ROW_NUMBER in SQL. Report option using textbox RunningSum property might be fastest performer.

score 0 · Answer 2 · answered Jan 13 '20 at 23:33

Assuming that you have a numerical primary key pk, then the following should yield the desired result:

select t.pk, t.id, -not exists (select 1 from Table1 u where u.id=t.id and u.pk<t.pk)
from Table1 t
order by t.pk

Change both occurrences of Table1 to the name of your table.

Bohemian · Answer 3 · 2020-01-14T20:40:23.320

Outer join the table to itself on the grouping column ID, but only where the primary key on the joined column is greater than the primary key of the main row. Then use fact that there won't be a joined column for the first (lowest primary_key) instance of a distinct ID value to generate either 1 or 0 accordingly:

select
    a.ID,
    if(b.ID is null, 1, 0) as is_unique
from mytable a
left join mytable b on b.ID = a.ID
  and b.primary_key > a.primary_key
order by a.primary_key

Access: Count the First Instance of Each Unique Value in a Column

3 Answers3