Search code examples
rankautonumber

autonumber/rank by a column in access

I have a list of claim numbers and I want to add a ranking column that changes when there is a new claim number.

If the claim number is the same, I want the ranking to go up by 1, but when there is a new claim number, I want the ranking to start over

I tried this but it just inserted 1 for every line

please help

SELECT

Inst.[Encounter Control Number],((Select Count(*) from Inst Where [Encounter Control Number] = [Encounter Control Number];)+1) AS Rank

FROM Inst order by [Encounter Control Number] ;

Solution

  • Please try similar to that:

    SELECT Inst.[Encounter Control Number],((Select Count(*) from Inst I2 Where [Encounter Control Number] < I1.[Encounter Control Number])+1) AS Rank 
FROM Inst I1
order by [Encounter Control Number] ;

    It will create a counter for each row.