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.

2 Responses to “counting dupes in Access”

  1. Dave Says:

    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.

  2. Arthur Says:

    select invoice_number, count(invoice_number) from allthoseinvoices_eh
    group by invoice_number
    order by invoice_number;