Duplicates records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone advise how to identify duplicate records in a table which has no
primary key. In otherwords check that the data in all fields is not
duplicated.
 
Take a look at the Find Duplicates Query Wizard that's presented as one of
the options when you create a new query.
 
The duplicate wizard restricts you to 10 fields. I need to check all fields
for duplicate data. Any other suggestions?

Thank & regards.
 
That's simply a limitation of the wizard, not of Access. Look at the SQL of
the query that's generated (you do this using the SQL View option under the
View menu while the query's open in Design view), and write your own SQL for
your required query.

Another approach would be to use the graphical query builder create a query
that selects all of the fields:

SELECT MyTable.Field1, MyTable.Field2, ... , MyTable.Fieldn
FROM MyTable

and then change that to:

SELECT MyTable.Field1, MyTable.Field2, ... , MyTable.Fieldn
FROM MyTable
GROUP BY MyTable.Field1, MyTable.Field2, ... , MyTable.Fieldn
HAVING Count(*) > 1
 
Hi,

I have tried this method. However I am not sure that I am doing it properly.
Do I switch the group function on. Then turn 'total:' to count in each field
then enter >1 into the 'criteria:' of each field.

Please advise.

Thanks again.
 
About which method are you asking? (I gave you two approaches)

In both cases, I'm suggested that you work directly with the SQL, which
means you won't have a Total: row, nor a Criteria: row.
 
OK. Understand now. However when I try to run it. It's telling me that the
level clause includes a reserved word or argument that is mispelled or
missing , or the punctuation is incorrect. I have used the code that you have
given me below.
Any suggestions?
 
Sorry, that's not really something you can automate. How will you know which
of the multiple rows you want to keep?
 
Back
Top