CODEWITHSUNDEEP
sqlms-access
Brant Barton
Brant Barton

Reputation: 458

Finding Duplicates via Query in Access

In my table I have a number of products. It's come to my attention that someone has uploaded some products using the same code. How can I run a query that will find all rows that are NOT unique in the productno field?

Upvotes: 1

Views: 226

Answers (2)

HansUp
HansUp

Reputation: 97131

As I understand the question, you want to see which rows include duplicate productno values, not just which productno values are duplicated.

If that's correct, select the duplicate productno values in a subquery and join your table to the subquery.

SELECT y.*
FROM
    [Your Table] AS y
    INNER JOIN
        (
            SELECT [productno], Count(*)
            FROM [Your Table]
            GROUP BY [productno]
            HAVING Count(*) > 1
        ) AS sub
    ON y.[productno] = sub.[productno]
ORDER BY y.[productno];

Upvotes: 2

Scotch
Scotch

Reputation: 3226

Try this

 SELECT productno, count(*) as num FROM Products GROUP BY ProductNo HAVING count(*)  > 1

Upvotes: 0

Related Questions