[AccessD] Help with code

To reverse the ranking:

SELECT T1.Student, T1.GradeLevel, T1.GPA, (SELECT Count(*) FROM
tblGrades WHERE GradeLevel=T1.GradeLevel) AS [Kids in Grade], (SELECT
Count(*) FROM tblGrades WHERE GradeLevel=T1.GradeLevel and GPA<=T1.GPA)
AS [Rank in Grade] FROM tblGrades AS T1;

The change is AND GPA<=T1.GPA instead of >= .

Just a note, the way this is going to calculate the 'rank', will have
some people with the same number:

Ie, 

Student GPA
Bob	2.1
Sue	3.8
Drew	3.8

This would return

1
2
2

Just a note.  It should have the same number for students with a 'tie'
grade.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Klos, Susan
Sent: Wednesday, September 26, 2007 12:52 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Help with code

=======Are you doing this in Access or Excel? 

 

Susan H. 

 

Susan:  I am in Accesst. <<Sue>>

 

No code necessary.  Here's an example of how to do this with a query, on

tblGrades.  There are three fields.  Student, GradeLevel and GPA:

 

SELECT T1.Student, T1.GradeLevel, T1.GPA, (SELECT Count(*) FROM

tblGrades WHERE GradeLevel=T1.GradeLevel) AS [Kids in Grade], (SELECT

Count(*) FROM tblGrades WHERE GradeLevel=T1.GradeLevel and GPA>=T1.GPA)

AS [Rank in Grade]

FROM tblGrades AS T1;

 

The last two columns will return the number of students in that grade,

and the rank of that student within that grade.

 

Drew

 

Drew, the query would work fine except I need it to rank the lowest
score as 1.  I tried sorting the score but neither ascending or
descending seems to make a difference. <<Sue>>

 

 

Susan Klos

Evaluation and Reporting

325 W. Gaines Street

Tallahassee, FL  32399

850-245-0708

susan.klos at fldoe.org <mailto:susan.klos at fldoe.org>
 

Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below.  Thank you in
advance for completing the survey.
 

http://data.fldoe.org/cs/default.cfm?staff=Susan.Klos@fldoe.org|13:52:00
%20Wed%2026%20Sep%202007
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.