autonumber/rank by a column in access

Question

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] ;

You must remove the ; in your subquery – Frank Jul 15 '15 at 21:46 — Frank, Jul 15 '15 at 21:46

score 0 · Accepted Answer · answered Jul 15 '15 at 21:53

0

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.

answered Jul 15 '15 at 21:53

Frank

1,901
20
27

thank you Frank, I get a dialogue box that asks me to enter a value for encounter control number. but it is definitely a column in my table. do you know how I can correct? – Ayesha Soto Jul 16 '15 at 14:00
I'm doing this in access not sql if that matters. – Ayesha Soto Jul 16 '15 at 14:07

score 0 · Answer 2 · answered Jul 22 '15 at 20:41

0

This will normally shown when access don't find the column. Is this not the name of your column? Please give me a screenshot of the table.

answered Jul 22 '15 at 20:41

Frank

1,901
20
27

autonumber/rank by a column in access

2 Answers2