Grouping a ranking query

[tim h]

The following ranking query works perfectly for a
single "b".

SELECT Q.a, Q.b, Q.c, Q.d, Q.e, Q.f, (SELECT COUNT(*) + 1
FROM qryi Q1 WHERE Q1.f > Q.f OR (Q1.f =
Q.f AND Q1.c < Q.c)) AS Rank
FROM qryi AS Q;

However, I want to be able to group this by "b" when
there are multiple records.

eg:

a b c d e f Rank
1 1 1 10 Ricahrd $10 1
1 1 2 20 Steve $9 2
2 2 1 30 Trevor $15 1
1 3 6 7 Jill $31 1
1 3 8 29 Jack $31 2

Rather than:

a b c d e f Rank
1 1 1 10 Ricahrd $10 4
1 1 2 20 Steve $9 5
2 2 1 30 Trevor $15 3
1 3 6 7 Jill $31 1
1 3 8 29 Jack $31 2

Can anyone offer a suggestion?
Thankyou

 

[Michel Walsh]

Hi,

.... WHERE ( Q1.f > Q.f OR (Q1.f =
Q.f AND Q1.c < Q.c) ) AND Q1.b=Q.b ) AS ...


Hoping it may help,
Vanderghast, Access MVP