0

I'm having some problems trying to make a rank in my database. I know that the MS Excel has an automatic formula to make the RANK, but MS Access doesn't have one and I'm kind of lost in the SQL programming formula.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Catarina Sequeira
  • 9
  • 1
  • 2
    afaik, there is no built-in `rank` function in access. You'll have to replicate it using some SQL. Please share more about your data, current SQL, desired results, etc, and perhaps someone here can help you further. Right now, your question is not detailed enough. – sstan Jul 26 '16 at 19:49
  • This sounds like asking for a row number in your query - assuming your query is sorted in order. Look at duplicate http://stackoverflow.com/questions/14683226/how-to-show-row-number-in-access-query-like-row-number-in-sql – dbmitch Jul 26 '16 at 22:33
  • Review http://allenbrowne.com/ranking.html. Post your attempted query for analysis. – June7 May 19 '17 at 19:25

1 Answers1

0

While Access does not have a 'RANK' function, it can order the results of a query, and within that query tell you a field that tells you if the record is first, second, third, etc. You can do this in two ways, you can check for the total number of records that are greater or less (depending on which way you are ranking) than the record you are on, and you can also decide if you want to 'tie' rankings by adding additional criteria. It takes a nested SQL statement to do this, but if you can upload a copy of your table structure i will be happy to help you more.

June7
  • 19,874
  • 8
  • 24
  • 34
Destin Consulting
  • 86
  • 5