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

suppose have table t following fields:

t(f1, f2, f3, f4, f5)

where combination of f1 & f2 forms primary key table.

what access sql query select following: t(f1, f2, f3, f4, f5, f4_f5_count)

where f4_f5_count count of duplicates of combination of fields f4 , f5 only (i.e. not fields being selected last two).?

you can use subquery this:

select t.*,        (select count(*) t t2 t2.f4 = f.f4 , t2.f5 = f.f5       ) fr_f5_cnt t;

or, use join aggregation:

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

edit:

you use second method , add:

where f4_f5_count = 1

Brant