How to code Microsoft access to automatically calculate student position (1 Viewer)

[Slim Shamble]

Hello,

I am still new in Microsoft access database... I have a student database that automatically calculate student report card at the end of the term.... Everything is working perfectly but i dont know how to code the program to automatically calculate student position(1st, 2nd, 3rd, 4th........) based on the student average in the class.

Is this possible to be done on Microsoft access?
if yes, can you be of help?

Warm reply will be appreciated
Best regard

 

[plog]

This can be done with SQL (via a query) so you don't need VBA. The simplest way is with a DCount (http://www.techonthenet.com/access/functions/domain/dcount.php). Basically, you count the number of students with a higher average than the current record, for each record and add 1 to it.

If you can post sample data to demonstrate what you have I could walk you through it. Either upload your database, an excel sheet with sample data (including table/field names) or use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name...
Sally, Math, 88
David, English, 67
Sally, English, 91
Tim, History, 79

 

[Slim Shamble]

Hey Thanks plog.... Can i drop it straight to your mailbox?

 

[Cronk]

What plog is referring to is something like

select Student, Score, DCount("Student","YourTable","Score>" & [score])+1 as position from YourTable order by Score desc;

 

[plog]

Yes you can, however, Cronk's code shows what I mean--it gets you 90% of the way there, you just need to replace the appropriate fields/tables with yours.

If you still need help, I will send a private message and you can reply to that.

 

[sneuberg]

Maybe something like:

SELECT [Results System].[Student Name], [Results System].Subjects, [Results System].Total_Score, DCount("*","[Results System]","[Subjects] = '" & [Subjects] & "' And  [Total_Score] > " & [Total_Score])+1 AS Rank
FROM [Results System]
ORDER BY [Results System].Subjects, DCount("*","[Results System]","[Subjects] = '" & [Subjects] & "' And  [Total_Score] > " & [Total_Score])+1;

but this gives students with the same score the same position. Is that ok?

 

[Slim Shamble]

I tried the codes but they aren't working. the error i received is missing operator. What am i missing guyz?

Sneuberg, the code u posted could have been more easier for me but some part of the code is missing.

Can you please paste it on a text file and drop it here....

 

[Slim Shamble]

Plog, i dont undastand your code... am getting errors....

I need more help please

 

[plog]

I haven't posted code on this.

 

[Slim Shamble]

Oh so sorry, that was cronk

 

[Slim Shamble]

Thanks Sneuberg, its worked but not as expected, later on, and i discover something, I dont understand how the position are being calculated but more than one student with different average are getting the same position which is not normal based on what i explained earlier.

is like my question wasnt fully understood.

i have a field which automatically calculate and display student average based on result data entered. Now, my question is that, is there no way the position of a student in a class can be gotten from a student average field. For example, the highest average is the 1st in the class, is the second highest average is the 2nd and so on....

Guyz..... i appreciate all ur efforts.

 

[sneuberg]

Please tell where the Ranking Query in the new,accdb database I emailed you isn't working the way you want. Give me the Student Names, Subject and Totals where "more than one student with different average are getting the same position".

 

[plog]

Still waiting for that data you posted in your 2nd post:

If you can post sample data to demonstrate what you have I could walk you through it. Either upload your database, an excel sheet with sample data (including table/field names) or use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name...
Sally, Math, 88
David, English, 67
Sally, English, 91
Tim, History, 79

 

[Slim Shamble]

Sneuberg, i finally understood how you got the position. Corrrect me if am wrong, the position is gotten from Total Score for the specify subject.

i actually want the position from the average field in the student details.

These are the list of students with different average but the same position. Following the format:

Student Name; Subject; Total Score;
Garrison Gason; English ; 57
Basil Benette; English; 76

Sneuberg, i changed more data and added more records

In my country, we dont get student position in a report system like this. We get position based on the overall average for the entire Total score.

i hope is starting to get clarified now

Plog, Even the compressed database is larger than the maximum size, so i might need your email to send it to you.

 

[sneuberg]

I don't have any of that in the copy of the database I have; just the Results System and Students tables. If you send me a database with student details I''ll create a query for you.

 

[Slim Shamble]

i just checked the database you sent me... the student details is there. Is a form not a query, You can use it to generate the query..

The average is right there in the Library and result portal.

 

[sneuberg]

I emailed you the database with a query named qryStudentAverages which calculates the average for the students like:

SELECT Students.[First Name], Students.[Other Name], Avg([Results System].Total_Score) AS AvgOfTotal_Score
FROM Students INNER JOIN [Results System] ON Students.ID = [Results System].[Student Name]
GROUP BY Students.[First Name], Students.[Other Name];

This query is used by a query qryRanking which uses a Dcount to calculate the ranking. The SQL for this query is:

SELECT qryStudentAverages.[First Name], qryStudentAverages.[Other Name], qryStudentAverages.AvgOfTotal_Score, DCount("*","[qryStudentAverages]","[AvgOfTotal_Score] > " & [AvgOfTotal_Score])+1 AS Ranking
FROM qryStudentAverages
ORDER BY qryStudentAverages.AvgOfTotal_Score DESC;

There are no subjects in this output as this considers the average for all subject as is the average shown in the Student Details form.