Rank in Calculated Query????

[printsol]

hi
I am new in access and just start learning
please can any body help me with this query

SELECT O.RollNo, O.StudentName, O.Class, O.Section, O.urduS1P1marks AS URDU, O.englishS1P1marks AS ENGLISH, O.mathS1P1marks AS MATH, O.scienceS1P1marks AS SCIENCE, K.UrduT, K.EnglishT, K.MathT, K.ScienceT, K.SubjectGT, iif(Urdu>=UrduT/2,Urdu,0)+Round(iif(English>=EnglishT/2,English,0)+iif(Math>=MathT/2,Math,0),2) AS SecuredTotal


FROM Semester1TBL AS O, Semester1SubjectTotalMarks AS K
ORDER BY O.Class, O.Section, O.Gender;

how can i rank(student position) by SecuredTotal

Thanks

[isladogs]

Have a look at my article Rank Order Queries - Mendip Data Systems.
This describes one method of doing this using a Serialize function. It also has a link to another method using subqueries.

The attached example database includes examples on rank ordering student marks

[printsol]

Thanks for Reply

SELECT qryCustomerValue.CustomerID,
qryCustomerValue.TotalValue,
(SELECT Count([CustomerID]) AS HowMany
FROM qryCustomerValue AS Dupe
WHERE Dupe.TotalValue > qryCustomerValue.TotalValue)
AS BeatenBy
FROM qryCustomerValue;
actually this method(query) work fine when i save my total in database table but how can i do when i calculating values in query like SecuredTotal((sum)adding some fields from table)

Thanks

[isladogs]

If you use the Serialize function you can rank on any field including calculated fields.
Suggest you save your aggregate query the create a rank order query based on the saved query as a record source.

[printsol]

i am getting this result please can u assist
regards

[isladogs]

I can't assist from that screenshot as I've no idea what you've done.
Upload your data and the query you used then I'll look if I have time or someone else will do so.

[printsol]

i just attached my database file,
there r only two simple table,
i am newbie in access and looking for help
regards

[isladogs]

Rank order queries aren't a beginner level topic and you have much to get right before you attempt anything like this

The good news is that I've fixed it for you. The rank order query output is now correct:


Look at both queries to see what I changed

There was a data corruption issue with Zaryab Khurram who was wrongly shown as rank 1 (equal)
I've re-entered the data and his rank is now correct

However you should treat this as a TEMPORARY SOLUTION and not the final method

The bad news is that your data structure is basically a spreadsheet and is totally inappropriate for a database:


Unlike Excel, database tables should have few fields but lots of records
You need to read up about normalisation and modify the structure of both your tables BEFORE attempting to do anything else.
If you continue with this structure, you will be fighting against Access all the way. Either change the structure or use Excel (which is probably where this data came from originally)

I don't have time to explain the concept of normalisation fully but hopefully someone else will go through this structure properly for you

For a start you need to
a) Split the Semester1TBL into at least 3 tables - tblStudents, tblClasses & tblMarks - possibly more
a) Calculated fields are usually a bad idea. Remove all of them. Do all calculations in queries
b) Having multiple fields with almost identical names is incorrect - replace all the marks fields with just 2 fields - Mark (number) & SubjectTest (short text) e.g. 6.50, UrduS1P1)

So tblMarks will have these fields MarkID (autonumber PK), RollNo, Mark, SubjectTest.
It means you will have multiple records for the same student - 1 record per test mark (see my example app on the website for further guidance)

This may seem like a lot of work now but please don't ignore it. Get the structure right now & it will save you lots of time in the future

Good luck

[printsol]

Thank you very much for you help and guidance , i will try to change data structure as per your recommendation , One more thing please let me know how can we dense rank?
for tie duplicate values it might be 1,2,3,3,4,5,6
any guidance

thanks a lot

[isladogs]

Thank you very much for you help and guidance , i will try to change data structure as per your recommendation , One more thing please let me know how can we dense rank?
for tie duplicate values it might be 1,2,3,3,4,5,6
any guidance

thanks a lot

Not quite sure what you mean.

To clarify, the first query sorts the students in descending mark order - highest mark at the top
The second query uses that to give a rank order value depending on which field is used in the Serialize expression

The example I sent you gives equal rank values where the GTotal marks are the same - e.g. three values where Rank =21 in screenshot from post #8
That is because the query SQL I used was:

SELECT GetClassDataFrmTable.RollNo, GetClassDataFrmTable.StudentName, GetClassDataFrmTable.Class, 
GetClassDataFrmTable.Section, GetClassDataFrmTable.Gender, GetClassDataFrmTable.Gtotal, Serialize("GetClassDataFrmTable","Gtotal",[Gtotal]) AS MarkRank
FROM GetClassDataFrmTable
ORDER BY Serialize("GetClassDataFrmTable","Gtotal",[Gtotal]);

If you don't want rank order ties, then base your serialize expression on RollNo instead:

SELECT GetClassDataFrmTable.RollNo, GetClassDataFrmTable.StudentName, GetClassDataFrmTable.Class, 
GetClassDataFrmTable.Section, GetClassDataFrmTable.Gender, GetClassDataFrmTable.Gtotal, Serialize("GetClassDataFrmTable","RollNo",[RollNo]) AS MarkRank
FROM GetClassDataFrmTable
ORDER BY Serialize("GetClassDataFrmTable","RollNo",[RollNo]);

This gives the result:


Please read my website article and look at the examples again as all of that was explained in that article - the link is in post #2

[printsol]

some thing like this

[isladogs]

Not possible with any rank order code.
Nothing to do with Access but that's not how numbers following equal rank values ever work

[NoellaG]

If you need more ranking functions, you can try SQL express as free database. T-SQL has more possibilities than Access SQL and you can link the access front-end with ODBC to the database. But only do so after you have reviewed the data structure.