Question Cant rank according to filter in query

[matiyas]

hallo! would really appreciate your help here,
so i have created a table called "tblStudents" with studentId,Name and marks as the fields in it.
i have also created a query to rank the students according to there marks obtained. The query works fine with a Ranked output 1 2 3 4 5....etc BUT! when i try to filter with a certain criteria say.. >=50 (on the marks field)
i still get the same Rank results NOT based on the new filter.
What i want is: the query to rank based on the filter.

i use the following sub query.
SELECT T2.Student_ID, T2.Students, T2.Marks, (SELECT COUNT(T1.Marks)
FROM

AS T1
WHERE T1.Marks >= T2.Marks) AS Rank
FROM

AS T2
ORDER BY T2.Marks DESC;

 

[matiyas]

thanks but please see the attached database to get exactly what i mean.
open the query and on the criteria of marks wright <50
you will see the rank of the students still continues with 7 8 9 instead of 1 2 3 based on the new criteria.

 

[madefemere]

Hello,
Here an SQL CODE to do what you want.

SELECT T2.Student_ID, T2.Students, T2.Marks, (SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks >= T2.Marks AND T2.Marks>50) AS Rank
FROM [Table] AS T2
ORDER BY T2.Marks DESC;

Then, you must filter on Rank field to show only the values different of Zero with :

SELECT T2.Student_ID, T2.Students, T2.Marks, (SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks >= T2.Marks AND T2.Marks>50) AS Rank
FROM [Table] AS T2
WHERE ((((SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks >= T2.Marks AND T2.Marks>50))<>0))
ORDER BY T2.Marks DESC;

I hope it helps you.
Good continuation

 

[matiyas]

okey thank you very much madefemere , that solves part of the problem.
However,I would like the user to insert random variables...more like a parameter query on the criteria..like the user could put >50, <=29...etc BUT still maintain the Rank and based on the new filter.
====================================================================
That was a smaller scale of the problem, in a larger scale i have a table of students from different classes, a single "query" calculates the Totals,grades them and Ranks them.
now on the criteria of that "query", on the "class field", if i filter to only students in say, "class A"...it does that but the Rank is Not taking the new filter but ranks according to the original query results of all students totals,regardless of class.
:banghead:

please this is Not about Normalization, i am greatly aware it can easily be solved that way.
just that i need this method to work, am trying to make my database as custom as possible.
THANKS IN ADVANCE.

 

[madefemere]

Hello,
Here is a DB sample that I hope helps you to get the right result.
For the first point, I don't think you can do it with a simple parameter query. You must create a form and use a vba code to allow your users to choose the comparaison sign. It will be fixed with a parameter query.

For the second point, It is possible changing a little your first code.

With the DB attachment,
1 - I add a field "Class" for students. We use a table here, but you can begin from a query with the same structure.
2 - The query Q_Class_Students is a parameters query that filters the students with the Marks superior of a value you give.
3 - The query Q_OrderStudentByClassWithExtremeMarks is a query based on the Q_Class_Students and orders students by ranks and filtered the class be choosen in parameter.

You see here that you can't change the comparaison sign

So the 3 others queries were used with the form to have the result. You choose comparaison sign, the value(50 as default value) and the class and run with the Filter button.

 

[Brianwarnock]

I do not have access to Access at the moment but I will make one comment, which if I understand Madefemere's last post is what he is saying , and that is that the ranking must be done after all other filtering has finished, in other words you will do the ranking as the last query on the result of the next to last.

Brian

 

[matiyas]

madefemere, SIR!
U HAVE SAVED MY LIFE!
EXACTLY WHAT I WANTED WITH A SUPER BONUS!
Almighty Bless You.