SQL query in Access to count records on the subset of of non-primary fields

Question

Suppose I have the table T with the following fields:

T(F1, F2, F3, F4, F5)

where combination of F1 & F2 forms a primary key for this table.

What would be the Access SQL query to select the following: T(F1, F2, F3, F4, F5, F4_F5_Count)

where F4_F5_Count is a count of duplicates of the combination of fields F4 and F5 only (i.e. not all fields that are being selected but only last two).?

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-07-13T17:55:14

You can use a subquery for this:

select t.*,
       (select count(*) from t as t2 where t2.f4 = f.f4 and t2.f5 = f.f5
      ) as fr_f5_cnt
from t;

Or, use join with an aggregation:

select t.*, tt.f4_f5_count
from t join
     (select f4, f5, count(*) as f4_f5_count
      from t
      group by f4, f5
     ) tt
    on t2.f4 = f.f4 and t2.f5 = f.f5;

EDIT:

You would use the second method and add:

where f4_f5_count = 1

SQL query in Access to count records on the subset of of non-primary fields

1 Answers