How to return a count of duplicates and unique values into a column in Access

Question

I currently have this table:

First_Name	Last_Name
Jane	Doe
John	Smith
Bob	Smith
Alice	Smith

And I'm looking to get the table to look for duplicates in the last name and return a value into a new column and exclude any null/unique values like the table below, or return a Yes/No into the third column.

First_Name	Last_Name	Duplicates
Jane	Doe	0
John	Smith	3
Bob	Smith	3
Alice	Smith	3

OR

First_Name	Last_Name	Duplicates
Jane	Doe	No
John	Smith	Yes
Bob	Smith	Yes
Alice	Smith	Yes

When I'm trying to enter the query into the Access Database, I keep getting the run-time 3141 error.

The code that I tried in order to get the first option is: SELECT first_name, last_name, COUNT (last_name) AS Duplicates FROM table GROUP BY last_name, first_name HAVING COUNT(last_name)=>0

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-07-07T20:00:58

You can use a subquery. But I would recommend 1 instead of 0:

select t.*,
       (select count(*)
        from t as t2
        where t2.last_name = t.last_name
       )
from t;

If you really want zero instead of 1, then one method is:

select t.*,
       (select iif(count(*) = 1, 0, count(*))
        from t as t2
        where t2.last_name = t.last_name
       )
from t;

How to return a count of duplicates and unique values into a column in Access

1 Answers