counting dupes in Access
Posted by joy
I have a Microsoft Access question that I know the solution is probably exceedingly easy but I can’t seem to find it.
What I want to do is count the number of times a particular ID is duped. I created a “find duplicates” query, and “grouped by” in the particular field and then created an expression to count the dupes exp1:count([fieldname]). However, all that the count does is count each individual dupe and it only returns a 1 for each dupe. Sum() doesn’t work in the expression and neither does RunSum().
My MSSQL skills are rusty so that’s why I was trying to create the darn expression in the query design window.
Subscribe to Cleverhack 
February 27th, 2005 at 8:55 pm
My first guess would be something like
select somefield, count() from sometable group by somefield having count() > 1
Instead of “somefield”, you could use a bunch of fields and it will tell you how many times each combination occurs. The having clause restricts it to combinations that occur more than once.
February 27th, 2005 at 9:44 pm
select invoicenumber, count(invoicenumber) from allthoseinvoiceseh
group by invoicenumber
order by invoice_number;