Duplicates

[Guest]

Problem:
I have a query with a list of trimmed e-mail address I need to list entries
that contain more the 5 duplicates.

Query name = qryTRimE_MailAfterTheAtSymbol
Field name = trimmed
Data Example = Joe@
tom@
Joe@
Joe@
Joe@
Joe@
Joe@
Would Be = Joe@
tom@

Thanks
Mike Sundman

 

[Guest]

Hi yooper_ssm,

Try use a "DISTINCT" in your query.

SELECT DISTINCT field1, field2, ...
..
..

 

[Guest]

Sorry JL

I messed up. I would like the query to give me a count of how many unique
records were in the table.

Query name = qryTRimE_MailAfterTheAtSymbolData Example = Joe@
tom@
Joe@
Joe@
Joe@
Joe@
Joe@
trimmed | count
Would Be = Joe@ | 6
tom@ | 1

 

[Guest]

Well,

You have to use group by (the Summation sign on the bar or they called it
"Totals").
the query would look like this,
SELECT qryTRimE_MailAfterTheAtSymbol.trimmed,
Count(qryTRimE_MailAfterTheAtSymbol.trimmed) AS CountOfequvilantband
FROM qryTRimE_MailAfterTheAtSymbol
GROUP BY qryTRimE_MailAfterTheAtSymbol.trimmed;

 

[Wolfgang Enzinger]

Mike,

I messed up. I would like the query to give me a count of how many unique
records were in the table.

Query name = qryTRimE_MailAfterTheAtSymbol
Data Example = Joe@
tom@
Joe@
Joe@
Joe@
Joe@
Joe@
trimmed | count
Would Be = Joe@ | 6
tom@ | 1

try like this:

SELECT trimmed,COUNT(*) FROM tablename GROUP BY trimmed

Cheers,
Wolfgang

 

[Guest]

Thanks Works Great!

 

[John Spencer (MVP)]

Use a totals query.

SELECT Trimmed
FROM qryTRimE_MailAfterTheAtSymbol
GROUP BY Trimmed
HAVING Count(Trimmed) > 5