Hi all,
I'm working on a student database (Access 2000)that keeps track of exam
scores. For each student, I need to determine the percentage of students
that scored higher than she/he did. Does anybody know how to do this using vba?

Thanks in advance.
Wambui

> Hi all,
I> 'm working on a student database (Access 2000)that keeps track of exam
s> cores. For each student, I need to determine the percentage of students
t> hat scored higher than she/he did. Does anybody know how to do this 
using vba?

> Thanks in advance.
W> ambui

Let me try this again, somehow last message got posted before I even 
started!

You will need to use a subquery for this.  The subquery can be designed to 
return the count of all testscores greater than the subject score. Let's 
say your tests are in a table named Tests.  The subquery would be 
something like this (I'm taking alot of liberties in naming fields):

Select count(*) from Tests as T where T.Score > Tests.Score

I started down that road too, but then I re-read the post, and it said "how
do I do this in VBA" not "in SQL"...

So...

I came up with this

(assuming you have a field in the table with the test scores, so that table
would have StudentID, Score, Percentile)
(assuming you have an ADO connection to your database)

dim rs as new adodb.recordset
dim C as int

C = Dcount("StudentTest") ' total # students

rs.open "StudentTest", Connection, adOpenKeyset
do while not rs.eof
    H = DCount("StudentTest","Score > " & cstr(rs!Score))
    rs!Percentile = 100 * (H / C)
    rs.update
    rs.movenext
loop

rs.close




-----Original Message-----
From: Mark Liquorman [mailto:Mark@L...]
Sent: Thursday, November 21, 2002 8:02 AM
To: Access
Subject: [access] Re: Calculation problem


Let me try this again, somehow last message got posted before I even 
started!

You will need to use a subquery for this.  The subquery can be designed to 
return the count of all testscores greater than the subject score. Let's 
say your tests are in a table named Tests.  The subquery would be 
something like this (I'm taking alot of liberties in naming fields):

Select count(*) from Tests as T where T.Score > Tests.Score