434,998 Members | 2,871 Online
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
4 Replies

 Expert Mod 15k+ P: 31,492 Table = tblResult Expand|Select|Wrap|Line Numbers No; Numeric; PK (Primary Key) J1; Numeric J2; Numeric J3; Numeric J4; Numeric J5; Numeric Expand|Select|Wrap|Line Numbers SELECT [No],        [J1],        [J2],        [J3],        [J4],        [J5],        [J1]+[J2]+[J3]+[J4]+[J5] AS [Total]        (SELECT Count(*)         FROM [tblResult]         WHERE ([J1]+[J2]+[J3]+[J4]+[J5])>[Total])+1 AS [Rank] 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

 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

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