Help needed with increment column that resets 1

[teach314]

hi - I have a query that has this structure...

 [b] qryTEST 

A           B1   B2   B3     C1   C2[/b]
3            2    0    0      6    0
3            2    0    0      6    1
3            2    1    1      4    2
3            3    1    0      2    6
5            0    0    1      3    2
5            0    4    0      9    1
5            1    0    1      0    0

The numbers aren't important here, but the records are properly ORDERed BY A, B1, B2, B3, C1, C2.
The actual query has about 1000 records.

I need help adding a column that increments from 1, but that restarts at each new value of A. In the sample shown, the revised query output should look like...

 [b]
A    Rank    B1   B2   B3     C1   C2[/b]
3      1      2    0    0      6    0
3      2      2    0    0      6    1
3      3      2    1    1      4    2
3      4      3    1    0      2    6
5      1      0    0    1      3    2
5      2      0    4    0      9    1
5      3      1    0    1      0    0

Thanks in advance for any assistance.

Teach314

 

[dhookom]

Are the B and C fields all single digit integers between 0 and 9?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

One way:
SELECT D.A,
(SELECT COUNT(*) FROM qryTEST WHERE A=D.A AND B1*10000+B2*1000+B3*100+C1*10+C2<=D.B1*10000+D.B2*1000+D.B3*100+D.C1*10+D.C2) AS Rank
, D.B1, D.B2, D.B3, D.C1, D.C2
FROM qryTEST AS D
ORDER BY D.A, D.B1, D.B2, D.B3, D.C1, D.C2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another way:
SELECT D.A, COUNT(*) AS Rank, D.B1, D.B2, D.B3, D.C1, D.C2
FROM qryTEST AS D INNER JOIN qryTEST AS C ON D.A = C.A
WHERE C.B1*10000+C.B2*1000+C.B3*100+C.C1*10+C.C2<=D.B1*10000+D.B2*1000+D.B3*100+D.C1*10+D.C2
GROUP BY D.A, D.B1, D.B2, D.B3, D.C1, D.C2
ORDER BY D.A, D.B1, D.B2, D.B3, D.C1, D.C2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[teach314]

hi DHookom - the B and C values are all between 1 and 99

 

[teach314]

Thanks PHV

I was very close to this SQL based on a solution you gave on 28 Jan 09, but I had mishandled the WHERE statement. Thanks for a great solution. ( I changed the <= to >= )

Teach 314