CODEWITHSUNDEEP
sqlms-accessms-access-2010
user716255
user716255

Reputation: 443

MS Access - Find duplicate values by concatenating some columns

I have a table with 5 columns. I need to find duplicate values using the first 3 columns.

Here is an example of my dataset:

enter image description here

Here is the output I need:

enter image description here

Here is the script that I tried to use:

select 
    count ([Tracking id] as [Count],
    [Hierarchy], 
    [Requirement ID], 
    [Tracking ID2], 
    [Status]
From 
    [table 1]
group by
    [Hierarchy], 
    [Requirement ID], 
    [Tracking ID2], 
    [Status]
having 
    count ([Tracking id]) > 1

Obviously this is not going to work because you can't group [Tracking ID2] and [Status].

I need a work around that would work in MS Access.

Upvotes: 1

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You seem to want:

select [Hierarchy], [Requirement ID], min([Tracking ID2]) as [Tracking ID2], min([Status]) as status
From [table 1]
group by [Hierarchy], [Requirement ID] 
having count(*) > 1;

status should not be in the GROUP BY clause.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

you can use subquery to achieve this

select 
    [Tracking id],
    [Hierarchy], 
    [Requirement ID], 
    [Tracking ID2], 
    [Status]
from
 [table 1] 
where [Tracking id] in (
     select 
       [Tracking id]
     From 
       [table 1]
     group by
       [Tracking id]   
     having count ([Tracking id]) > 1)

or 

 where exists (
         select 
           1
         From 
           [table 1]
         group by
           [Tracking id]   
         having count ([Tracking id]) > 1)

to include count(), this is one of the options, by using join

  select 
        t1.ct,
        t1.[Tracking id],
        [Hierarchy], 
        [Requirement ID], 
        [Tracking ID2], 
        [Status]
  from [table 1] t
  inner join
    (select 
        [Tracking id],
        count(1) as ct
    from [table 1]  
    group by [Tracking id]   
    having count ([Tracking id]) > 1)) as t1 on t1.[Tracking id] = t.[Tracking id]

Upvotes: 2

Related Questions