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

How to get the Total and getting the Ranking of the Totals using query in MSAccess?

P: 2
Can someone help me get the total and getting the ranking of the totals following the data below using a query:

No - J1 - J2 - J3 - J4 - J5 - Total - Rank
01 - 10 - 10 - 15 - 20 - 30 - 85 - 2
02 - 10 - 15 - 15 - 10 - 10 - 60 - 4
03 - 15 - 20 - 20 - 15 - 25 - 95 - 1
04 - 20 - 10 - 10 - 10 - 10 - 60 - 4
05 - 15 - 15 - 20 - 10 - 20 - 80 - 3


Any help will be deeply appreciated. Thanks in advance!!!
Feb 7 '08 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Table = tblResult
Expand|Select|Wrap|Line Numbers
  1. No; Numeric; PK (Primary Key)
  2. J1; Numeric
  3. J2; Numeric
  4. J3; Numeric
  5. J4; Numeric
  6. J5; Numeric
Expand|Select|Wrap|Line Numbers
  1. SELECT [No],
  2.        [J1],
  3.        [J2],
  4.        [J3],
  5.        [J4],
  6.        [J5],
  7.        [J1]+[J2]+[J3]+[J4]+[J5] AS [Total]
  8.        (SELECT Count(*)
  9.         FROM [tblResult]
  10.         WHERE ([J1]+[J2]+[J3]+[J4]+[J5])>[Total])+1 AS [Rank]
  11. FROM [tblResult] AS [tblOuter]
Credit goes to Rabbit for introducing me to this method in Adding a Rank column to a report
Feb 7 '08 #2

NeoPa
Expert Mod 15k+
P: 31,492
Another method can be found in post #2 of How to rank a query in MS Access?
You can choose which method you prefer.
Feb 7 '08 #3

P: 2
Thank you very much sir for the immediate response. I'll try this.
Feb 8 '08 #4

NeoPa
Expert Mod 15k+
P: 31,492
No worries. I'm pleased to help :)
Feb 8 '08 #5

Post your reply

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