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.
4 Answers
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.
8 Comments
maro
This query shows more records than are in the table. I want only select records which names are not unique.
Rahul Tripathi
@maro:- Do you want to find duplicates in your table? If yes then I have updated my answer. If no, then please clarify!
maro
Rahul: yes. I changed the title of the question.
Rahul Tripathi
@maro:- Have you checked the EDIT which I made? That will resolve your purpose!
Rahul Tripathi
@maro:- You can also use the wizard by following the steps mentioned in here: howtogeek.com/howto/microsoft-office/…\
|
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)
Comments
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)
1 Comment
maro
I have only one table. These queries show an empty table. So it's not the same what I asked.
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.
1 Comment
maro
So how can I select all records from a table where names are not unique?
selectstatements and MS Access doesn't supportEXCEPT.