Student grade system

Question

Dear MVP,

pls tell details about a result sheet. I have student table and their marks. and i want the students' place according to their marks. 

Suppose,

Name        English           Math          Physics           Total           Place

Jim                 50                 60                  40             150                 2nd

Jac                  60                 40                  30             130                3rd

Mac                90                  50                 20             160                 1st

I want the formula for Place in Ms Access. pls help me. Remember I am new, so tell me details.

thanks

Answer 1

When you create a report, you can add a textbox in the Detail section and set the ControlSource property to "=1" and the RunningSum property to "Over Group" and Access will number the rows 1 through N. If you then also sort by Total descending, you have your numbering.

But not ranking as it does not cater for ties.

Answer 2

Firstly we must assume that the table you showed in your original post is the result table of a crosstab query.  If it's a base table then it's design is fundamentally wrong as Tom pointed out, so you need to go back to square one and redesign your database correctly.

If it is the result table of a crosstab query then, as far as I can tell from a few tests, the standard method of computing ranks in a query by means of a correlated subquery is not possible; you need to call the VBA DCount function instead.  So let's assume the query is named StudentResults, in which case the SQL statement for a query to return the rows ranked by Total would be:

SELECT *,

    DCount("*","StudentResults", "Total > " & Total)+1 AS Rank

FROM StudentResults

ORDER BY Total DESC;

Note that because of the use of the asterisk in the SELECT clause to return all columns from the original query's result table, the Rank column will be returned first in the final result table.

Note also that Karl's basis of computing ranks was not quite right.  You need to count the rows with a value greater than the current row and add one, not count the rows with a value greater than or equal to the current row.  Otherwise ties are ranked incorrectly.  With a base table it can be done with a subquery, as in this example from my on-line demo:

SELECT T1.TransactionDate, T1.TransactionAmount, CustomerID,

    (SELECT COUNT(*)+1

     FROM Transactions AS T2

     WHERE T2.TransactionAmount > T1.TransactionAmount) AS Rank

FROM Transactions AS T1

ORDER BY T1.TransactionAmount DESC;

or more efficiently with a JOIN:

SELECT T1.TransactionDate, T1.TransactionAmount, T1.CustomerID,

COUNT(T2.TransactionID)+1 AS Rank

FROM Transactions AS T1 LEFT JOIN Transactions AS T2

ON T2.TransactionAmount>T1.TransactionAmount

GROUP BY T1.TransactionAmount, T1.TransactionDate,

T1.CustomerID, T1.TransactionID

ORDER BY T1.TransactionAmount DESC;

I've not tested to see whether a JOIN can be used with a crosstab query's result table, but even it can it would not be a universally practicable solution in view of the need to specify the columns individually, which is not always possible with a crosstab query whose returned columns might vary unless explicitly specified by means of a value list in an IN clause.

Answer 3

This will give you ranking --

SELECT YourTable.SCORETOTAL, (SELECT Count(*) FROM YourTable AS [XX] WHERE [XX].SCORETOTAL >= YourTable.SCORETOTAL ) AS Rank

FROM YourTable

ORDER BY YourTable.SCORETOTAL DESC;

Nothing is working properly, pls suggest me to solve the problem. 

thnks

Answer 4

When you create a report, you can add a textbox in the Detail section and set the ControlSource property to "=1" and the RunningSum property to "Over Group" and Access will number the rows 1 through N. If you then also sort by Total descending, you have your numbering.

Note that fields like Total and Place do not belong in a table; they should be calculated values in a query.

Also note that your example is not the way to setup your table because it violates important database design principles (the prohibition on Repeating Groups). I assume what you are showing is the result of a crosstab query on a properly designed relational table.

Answer 5

This will give you ranking --

SELECT YourTable.SCORETOTAL, (SELECT Count(*) FROM YourTable AS [XX] WHERE [XX].SCORETOTAL >= YourTable.SCORETOTAL ) AS Rank

FROM YourTable

ORDER BY YourTable.SCORETOTAL DESC;