By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Rank ordering records in dynaset

P: 1
Here is an Access question for you.

Say I have a table of people’s weights and their names:

Joe 150
Pete 160
Dave 165

I can make a query than sorts in descending order:

Dave 165
Pete 160
Joe 150

No problem. But what if I want to assign a score based on weight. I.e. the highest weight gets the highest score. So, my output would be:

Dave 165 1
Pete 160 2
Joe 150 3

How do I get the scores in a calculated field? Is that possible in Access?
Oct 22 '08 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,186
You can do it with subqueries (Subqueries in SQL) :
Expand|Select|Wrap|Line Numbers
  1. SELECT Name,
  2.        Score,
  3.        (SELECT Count(Name)
  4.         FROM tblTest3 AS sT3
  5.         WHERE sT3.Score>tT3.Score)+1 AS Pos
  7. FROM tblTest3 AS tT3
Oct 23 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.