ACCESS Help 1

[scevbact]

I am looking for some help on a VBA function that would do the following

1) I Have a table that has one of the fields that has multiple values (Field1). An adjoining field is called ranking and it has null values. Thus for instance the value 411 could be repeated 2, or 3 times in Field1
2) I want the Function, or Procedure to look at the first value in Field1 and assign a 10 in the Ranking field. If there are more than one record with the same value (411) it would go to the second record and assign an increment of 10. So the second record with the value 411 in Field1 would have 20.
3) If the third record is, say 412, the ranking filed would reset to 10 and follow the same logics as in (2) above.

Thanks for your help

 

[MajP]

You have three records with 411, how can you determine which one is the first, second, third? Is there a date field, or auto number field? If there is you can do a ranking query, or you can also do this in code. If it does not matter as long as one is 10, one is 20 and one is thirty, that can also be done. Can you provide the sort field name if it exists. If there is not a sortable field then recommend adding an autonumber to the table.

 

[scevbact]

Please explain a ranking query?

 

[MajP]

A ranking query looks something like this

SELECT *, (select count(*) from tbl as tbl2 where
tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl