CODEWITHSUNDEEP
sqlms-accessduplicates
John P
John P

Reputation: 3

Access SQL: How to find duplicate records between dates

    SN            DATE
===========    =========
    111         1/1/2014
    222         2/1/2014
    333         3/1/2014
    111         4/1/2014
    222         5/1/2014
    333         6/1/2015
    222         7/1/2015
    111         8/1/2015
    333         9/1/2015
    111         10/1/2015
    111         11/1/2015

I have a table with 2 columns (SN and DATE). I would like to create a query that will find duplicate SN between 1/1/2014 and 31/12/2014. I want to count duplicates and show each row that is a duplicate with SN and DATE.

Upvotes: 0

Views: 1609

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

One method is to use exists:

select t.*
from table as t
where date between #2014-01-01# and #2014-12-31# and
      exists (select 1
              from table as t2
              where date between #2014-01-01# and #2014-12-31# and
                    t2.sn = t.sn and t2.date <> t.date
             );

However, this will not find an sn that has two records on the same date. For that, you can do:

select t.*
from table as t
where t.sn in (select t2.sn
                from table as t2
                where date between #2014-01-01# and #2014-12-31#
                group by t2.sn
                having count(*) >= 2
               );

Upvotes: 1

Gustav
Gustav

Reputation: 55841

Just filter out those with no dupe:

Select * From YourTable
Where 
    ([Date] Between #2014-01-01# And #2014-12-31#)
    And SN Not In 
    (Select T.SN From YourTable As T
     Where T.[Date] Between #2014-01-01# And #2014-12-31#
     Group By T.SN
     Having Count(*) = 1)

Upvotes: 0

Sachu
Sachu

Reputation: 7766

Try this

SELECT SN , Count(SN) AS Dup
FROM [TableName]
WHERE DATE BETWEEN #2014-01-01# AND #2014-12-31#
GROUP By SN
HAVING Count(SN) >1

Upvotes: 0

Related Questions