select additional columns not in dup checker query

Question

In Access, I'm identifying duplicate records based on 28 columns. I want to also select a non-duplicate column but can't figure it out.

tbl_1:

bud_line | fund | name      | amt
123      |  ABC |  BOB      | $8
123      |  ABC |  BOB ROSS | $8
321      |  ABC |  BOB      | $8
321      |  AAA |  BOB      | $8
321      |  AAA |  BOB      | $20
321      |  XXX | JOHN      | $10
321      |  XXX | JOHN      | $10

The only lines I want on the output would be:

123 | ABC | BOB      | $8
123 | ABC | BOB ROSS | $8
321 | XXX | JOHN     | $10

I was using a query like:

select bud_line, fund, amt
from tbl_1
group by bud_line, fund, amt
having count(*) > 1

I get my dups, but I don't get the names BOB & BOB ROSS to identify them.

I tried adding name to the select, but it errors because it's not part of the aggregate function.

If I add name to the select and group, then it's no longer a dup, and those are exactly the ones I'm trying to find. — user3023480

Gordon Linoff Gordon Linoff · Accepted Answer · 2014-06-02T20:41:10

In Access, you do this by joining back to the original table:

select t.*
from tbl_1 as t inner join
     (select bud_line, fund, amt
      from tbl_1
      group by bud_line, fund, amt
      having count(*) > 1
     ) as tt
     on t.bud_line = tt.bud_line and t.fund = tt.fund and t.amt = tt.amt
order by bud_line, fund, amt;

If you had a unique id on each row, you could do this with an exists clause, which could use an index and be faster:

select t.*
from tbl_1 as t
where exists (select 1
              from tbl_1 as tt
              where t.bud_line = tt.bud_line and t.fund = tt.fund and t.amt = tt.amt and
                    t.id <> tt.id
             );

select additional columns not in dup checker query

1 Answers