Rank Query

[Rakesh Pavithran]

Hello to All,

Iam an Access newbie and trying to solve the following problem that I have been confronted with

The following fields are there in the query named "Overall %"
1) Employment #
2) Last Name
3) First Name
4) Date of evaluation
5) Semester
6) Batch Name
7) Exam Score 1 - 6 (there are 6 fields)
8) Overall Percent (Overall Percent: ([Main Table]![Exam 2 Score]+[Main Table]![Exam 3 Score])*0.2+[Main Table]![Exam 4 Score]*0.25+[Main Table]![Exam 5 Score]*0.15+[Main Table]![Exam 6 Score]*0.2

I need to create a field "Rank" which will give me rank of my students based upon values from the field "Overall Percent"

Thank to every one responding and all you inputs and help will be greatly appriciated.

 

[Pete666]

quick and nasty would be to sort the data by the score and use an autonumber field to number them from 1 to..... whatever.

 

[Rakesh Pavithran]

Hello Pete666

Thanks for your relpy, I tried that but I need to give same rank to people with similar scores, What Iam looking for is the Access implementation of the "Rank" Function in Excel, I have read through threads with similar questions and tried out the suggestions, but fell flat on my face, my code writing skill at best is good at directing children in a game of treasure hunt, therefore this question

 

[raskew]

Hi -

If you're at the point where you've created your respective sums that look
something like this (sorry about the crummy formatting -- couldn't get it
to hold in the reply screen)

EmployeeId SumOfPerCent
1 70
2 82
3 73
4 83
5 82
6 68
7 80
8 73
9 80

...then copy/paste this SQL to a new query and modify table/query/field
names as applicable:

SELECT Query145.EmployeeId, Query145.SumOfPerCent, (SELECT
    COUNT(*) + 1 
FROM
   Query145 Q1     
WHERE
   Q1.SumOfPerCent > Query145.SumOfPerCent) AS Rank
FROM Query145 LEFT JOIN Query145 AS Q1 ON Query145.EmployeeId = Q1.EmployeeId
ORDER BY Query145.SumOfPerCent DESC;

...and it should result in this:

EmployeeId SumOfPerCent Rank
4 83 1
5 82 2
2 82 2
9 80 4
7 80 4
8 73 6
3 73 6
1 70 8
6 68 9

HTH - Bob

 

[Rakesh Pavithran]

Hello raskew,

Thanks for the info and guidance; I tried it and Iam getting the following message
"Check the subquery's syntax and enclose subquery in parentheses"

I have done a copy paste job of your code as suggested, changed field and query names only, I have rechecked for mistakes that I may have committed during rewriting your code, upon enclosing the code in parentheses I get the following error message

"Invalid Date Field"

Also - is there a way of running this within the existing query instead of creating a new query??

Thanks to all for patience and help

 

[raskew]

Hi -

"Check the subquery's syntax and enclose subquery in parentheses"

Since what was provided is from a tested, working query -- not air-code -- I'm going to guess that the problem is with field names. Anytime field names have spaces in them, as you've indicated (Employment #, Evaluation Date, etc..), they must be enclosed in brackets [ ] and failing to do so can lead to some parentheses problems. Might be a good time to tighten-up your naming
practices (e.g. EmploymentID, EvaluationDate or Evaluation_Date). Additionally, you should avoid special characters (#, %) other than the underscore ( _ ) in your table/query/field names.

"Invalid Date Field"

I'm unsure why you would need or want a date field in the query that sums up the test scores. Unless there's a reason for it, I'd remove it, then check for any reference to it in the query SQL.

This may come with the field-name problems discussed above, or it could be that your dates are not stored as date/time data type, but rather as strings. Open up the underlying table(s) and check the data type of each date. If this turns out to be the problem, don't change the existing field but rather add a new field in date/time data type. Then, run an Update query to
populate the new field with the DateValue() of the string-date. Only when you're satisfied that the new field has been correctly populated should you delete the string field and rename the new field to the name of the old field.

Also - is there a way of running this within the existing query instead of creating a new query??

In my experience, No. The source query is necessarily a totals query (needed to Sum the test scores) and this has interferred with all my attempts to do the ranking within this query. Perhaps someone else has a solution.

Please post back if you continue to have problems. Ensure you display a copy of your query SQL as it's difficult to determine what's going wrong without seeing exactly what's been created.

Good Luck - Bob

 

[Rakesh Pavithran]

Thank you Bob,
for taking the time and effort, will go and do as suggested by you and try to see if it works.

Regards - Rakesh

 

[Rakesh Pavithran]

Hello Bob,

Got it working perfectly, problems caused by some glitches at my end, you were right about nomenclature, solved all of it as suggested by you. sticking to the guidelines provided in future.

Thanks for the Help.
Regards
Rakesh

 

[raskew]

Hey -

Gotta love it when a plan comes together!

Best Wishes - Bob

 

[MSAccessRookie]

Hey -

Gotta love it when a plan comes together!

Best Wishes - Bob

Someone else who remembers the A-Team?

 

[raskew]

Why sure! Being very experienced (don't say old), I'm constantly surprised what previous thoughts and experiences can appear without notice.

Thanks for noticing - Bob

 

[georgedwilkinson]

First thing I thought of when I read Bob's response was the Robot Chicken parody of A-Team.

 

[raskew]

What a coincidence! First thing I thought of was McCain explaining Shiite vs Sunni.

Oh well -- Bob