Duplicate entries

[Davighi]

I have a database of 100,000+ entries and have some records that are duplicated.

how can I write a query to pull out all records that have two or more identical entries, and if possible can this be sorted by the number of duplicates of a particular entry

SELECT adverts.title
FROM adverts
WHERE ???

 

[NJudson]

Try something like this to get unique adverts.title and count #

SELECT adverts.title, Count[adverts].[title] AS TitleCount
FROM adverts
GROUP BY adverts.title
ORDER BY Count[adverts].[title] DESC;


I think this should work.

 

[Davighi]

NJudson

I tried that and got an error message, did I do something wrong?

Syntax error (missing operator) in query expression 'Count[adverts].[title]'.

 

[NJudson]

I'm sorry, try Count([adverts].[title])

I forgot the parenthesis

 

[Davighi]

Works great thanks

one last question
I tried adding other fields and it didn't work
message says:
you tried to execute a query that does not include the specific expression 'id' as part of an aggregate function.

SELECT adverts.id, adverts.title, adverts.name, adverts.url, Count(adverts.url) AS TitleCount
FROM adverts
GROUP BY adverts.url
ORDER BY Count(adverts.url) DESC;

Sorry for being stupid!

 

[NJudson]

Your not being stupid, I still fumble around trying to figure things out.

I think you may need to add the additional fields into the "GROUP BY" line like so:

SELECT adverts.id, adverts.title, adverts.name, adverts.url, Count(adverts.url) AS TitleCount
FROM adverts
GROUP BY adverts.url, adverts.title, adverts.id, adverts.name
ORDER BY Count(adverts.url) DESC;

 

[Davighi]

People are very helpful on this forum

thanks NJudson