Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here





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


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.


Please explain a ranking query?


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close