Please, i am building an application for an evening secondary school for adults. The owner of the school wants the application to keep records of the students, compute their result and generate results. Am having proplem in computing result and where am having problem is in positioning each student according to their average scores. I want to read the average of the students from the database and automatically create the position a student falls to and save the position to the student's row. For instance, if there are 5 students with Olu having 63, Chidi having 88, Kazeem having 51, John having 45 and Agbo having 38, it should assign 1st position to Chidi, 2nd position to Olu, 3rd position to Kazeem and so forth till the last which is the 5th position to Agbo. Please help me in any way. I use VB.NET

Are u working with mysql or ms access 4 database connection? Anyway post ur error code 4 rectify.

i havent tried any thing yet. Idont even know how to go about the algorithm much less the codeing. I use access database. I want it such that at any pint in time, a selected student will be assigned position in class.

Its simple. Use the ORDER by syntax in your query.

Like SELECT * from tbINFO order by grades

it is not solving my problem?

what is the error?

This is a database issue and not front end issue. If you understand your DB of choice well, you should be able to do this using query; first to compute (average) scores and then to display them in the desired order. Basic DB rule states that you don't store calculated figures in your DB.

Are you a graduate?
ARE you a Good Programmer / software engineer?
Are your very good in dot NET?
Are you already in PH or willing to relocate at your own expense?
If you meet the basic requirement above. Send your resume to bekky_ben@yahoo.co.uk
Your skill (Programminglang etc)
and your salary expectation per month. And i would getback to u. Also feel free to recommed young minds
Disregard this advert after 15th of August as an interview would be conducted before the 18th of August

I think I could help...
First you'll have to read all the scores from the database...
Put all the results in an array, Perform a sort operation on your array so it sorts each element from the highers to the lowest...
Then, you'll insert the positions based on the array index e.g the highest score would be at the top so all you'll do is use the index + 1 as the position.

This would work straightforward assuming you have a perfect collection of scores (i.e. no duplicates) but eve if you do, it'll just be a step further and I'll think up something once you'r able to do what I said successfully.

Cheers!

First of all.....did u design the database well? Is your connection string correct? And is it saving into the database? Check first. If yes
Use ds string SELECT * from grades. And make sure it is filtered by grades

Dear VURN, i think i understand ur challenge. you want to rank your result from ur query based on the average score. I will share a SQL snippet from our Schools Management ERP software.
/****** Object: StoredProcedure [dbo].[TermReportCard] Script Date: 08/10/2012 18:31:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TermReportCard]
(
@termId varchar(20),
@currentClass varchar(50),
@admissionNo VARCHAR(20)
)
--exec [TermReportCard] '1001201120121','jss2f boys','10/6911'
AS
SET NOCOUNT ON;

DECLARE @averageAge AS NUMERIC
--get average class age
SELECT @averageAge=AVG(DATEPART(Yy, Ap.DateOfBirth))
FROM Applicant ap INNER JOIN
AdmissionNumber an ON Ap.ApplicantId = an.ApplicantId
WHERE (an.CurrentClass = @currentclass)

SELECT @averageAge=DATEPART(yy,GETDATE())-@averageAge

-- get term and session info
DECLARE @term VARCHAR(20),@session VARCHAR(20)
SELECT @term=
CASE term
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
end
FROM dbo.Term WHERE TermID=@termId

SELECT @session=session FROM dbo.Term WHERE TermID=@termId

--add school letterhead, principal signature and
DECLARE @principalSign AS VARBINARY(max)
DECLARE @noInClass AS INT
DECLARE @percent AS decimal(8,2)

SELECT @principalSign=SIGNATURE FROM staffbio WHERE Designation='###'
SELECT @noInClass=COUNT(DISTINCT AdmissionNo) FROM dbo.StudentResults WHERE PresentClass=@currentClass AND TermID=@termId
-- determine students position
DECLARE @weightScore decimal(8,2),@noOfSubjects INT,@position int
SELECT @weightscore=SUM(aggregatescore)FROM dbo.StudentResults WHERE PresentClass=@currentClass AND TermID=@termId
AND AdmissionNo=@admissionNo
SELECT @noOfSubjects=COUNT(DISTINCT subject)FROM dbo.StudentResults WHERE PresentClass=@currentClass AND TermID=@termId
AND AdmissionNo=@admissionNo

SELECT @percent=@weightscore/@noOfSubjects;

WITH classPositions AS
(
SELECT admissionno, ROW_NUMBER() OVER (ORDER BY SUM(AggregateScore) DESC) AS 'POSITION' FROM studentresults
WHERE PresentClass=@currentClass AND TermID=@termId
GROUP BY AdmissionNo
)
SELECT @position=POSITION FROM classPositions WHERE admissionno=@admissionNo;

--select the main table
SELECT @averageAge 'Average Class Age',@position 'Position',@percent 'Percent',@noInClass 'No in Class',
(@noOfSubjects*100) 'Weighted Score', a.StudentName, AggregateScore,s.Subject,s.AdmissionNo,
@principalSign 'Principal',@term 'Term',@session 'Session',s.PresentClass,
s.Assessment FROM studentresults s INNER JOIN dbo.AdmissionNumber a ON s.AdmissionNo=a.AdmissionNo
WHERE s.TermID=@termid AND s.PresentClass=@currentClass AND a.AdmissionNo=@admissionNo


Take note of :
SELECT admissionno, ROW_NUMBER() OVER (ORDER BY SUM(AggregateScore) DESC) AS 'POSITION' FROM studentresults
WHERE PresentClass=@currentClass AND TermID=@termId
GROUP BY AdmissionNo
that is where the ranking takes place. If u need more assistance or clarification, contact me: david.adele@codebasetechnologies.com. Thanks

@Bros 1. Thanks alot for your assistance. You have given me another idea but what if two or three persons have the same average score? I will try this and see shaa. But i code with VB.NET

This problem you have is called Ranking issue.
TSQL provide ranking functions.

So when average scores are same for more than one student, you have 2 main options.

1. SELECT admissionno, RANK() OVER (ORDER BY SUM(AggregateScore) DESC) AS 'POSITION' FROM studentresults
WHERE PresentClass=@currentClass AND TermID=@termId
GROUP BY AdmissionNo
Result will be like this: 1,2,2,2,5,6,6,8,.....

2. SELECT admissionno, DENSE_RANK() OVER (ORDER BY SUM(AggregateScore) DESC) AS 'POSITION' FROM studentresults
WHERE PresentClass=@currentClass AND TermID=@termId
GROUP BY AdmissionNo
Result will be like this:1,2,2,2,3,4,4,5,.....

From experience with schools we've deployed. option 1 is preferred. But i may suggest in ur case like the client decide.
Thanks