How to find duplicates in a table using Access SQL?

Question

I try to use an SQL query in Access but it doesn't work. Why?

SELECT * FROM table
EXCEPT
SELECT DISTINCT name FROM table;

I have a syntax error in FROM statement.

Answer 1

MS Access does not support EXCEPT keyword. You can try using the LEFT JOIN like this:

select t1.* from table t1 left join table t2 on t1.name = t2.name

EDIT:

If you want to find the duplicates in your table then you can try this:

SELECT name, COUNT(*) 
FROM table 
GROUP BY name
HAVING COUNT(*) > 1

You can also refer: Create a Query in Microsoft Access to Find Duplicate Entries in a Table and follow the steps to find the duplicates in your table.

First open the MDB (Microsoft Database) containing the table you want to check for duplicates. Click on the Queries tab and New.

This will open the New Query dialog box. Highlight Find Duplicates Query Wizard then click OK.

Now highlight the table you want to check for duplicate data. You can also choose Queries or both Tables and Queries. I have never seen a use for searching Queries … but perhaps it would come in handy for another’s situation. Once you’ve highlighted the appropriate table click Next.

Here we will choose the field or fields within the table we want to check for duplicate data. Try to avoid generalized fields.

Name the Query and hit Finish. The Query will run right away and pop up the results. Also the Query is saved in the Queries section of Access.

Depending upon the selected tables and fields your results will look something similar to the shots below which show I have nothing duplicated in the first shot and the results of duplicates in the other.

Answer 2

use HAVING COUNT(name) > 1 clause

SELECT * FROM Table1
WHERE [name] IN 
(SELECT name, Count(name)
FROM Table1
GROUP BY name
HAVING COUNT(name)>1)

Answer 3

Whether Access supports except or not is one issue. The other is that you are not using it properly. You have select * above the word except and select name below. That is not valid sql. If you tried that in SQL Server, your error message would be All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Answer 4

You can use LEFT JOIN or EXISTS

LEFT JOIN

SELECT DISTINCT t1.NAME FROM table1 as t1
LEFT JOIN table2 as t2 on t1.name=t2.name
WHERE t2.name is null
;

NOT EXITS

SELECT T1.NAME FROM table1 as t1 where not exists
(SELECT T2.NAME FROM table2 as t2 where t1.name=t2.name)