hi
i need single query to get total marks and rank on base of total marks
hi
i need single query to get total marks and rank on base of total marks
How is your data stored?
Show us the underlying data, as at the moment it looks like an Excel spreadsheet, and that's not how it should be stored?
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
same like attached image except total and rank column
As you are using an excel structure, export to excel and calculate there?
Otherwise just sum the fields, but I cannot see how you can get the rank as well in one query?
Plus when you have another subject, you have to change the query.
Structure is ALL wrong
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
You should have a table with
RollNo , Subject, Mark.
It would then be a simple cross tab query to get your totals.
You can then add a ranking no if really necessary in a report, or use a sub query, based around the crosstab.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Maybe something along these lines would work for you, have a look at the qryRankWithTiesOneQuery query.
Cheers,
i have a query
getting total score from a query and rank it
SectionPos: (select Count( [AllResultQRY]![RollNo] ) as howmany from [AllResultQRY] as xyz where xyz.section = [TempVars]!CS and xyz.Swing=[TempVars]!CW and xyz.Sclass=[TempVars]!CC and xyz.ExamType=[TempVars]!CE and xyz.TotalSecured > [AllResultQRY]![TotalSecured] )+1
i got the result with ties
1
2
3
4
4
6
i want instead
1
2
3
4
4
5
any help?
Thanks
Consider:
Call the function in query:Code:Function Rank(intTot) Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [English]+[Math]+[Physics] FROM Grades WHERE [English]+[Math]+[Physics] >= " & intTot) rs.MoveLast Rank = rs.RecordCount End Function
SELECT Grades.RollNo, [English]+[Math]+[Physics] AS Total, Rank([English]+[Math]+[Physics]) AS Rank
FROM Grades
ORDER BY Rank([English]+[Math]+[Physics]);
Result:
If you really want ranking reversed, change the >= operator to <= in the function.
RollNo Total Rank 4 24 1 5 23 2 3 23 2 1 20 3 2 18 4 6 17 5
Unfortunately, will probably perform slowly with large dataset. As will probably almost any solution for ranking records. Another approach is VBA writing records to a 'temp' table.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
is there any way to do it with query with our vb
Colin, which technique will produce sequence that doesn't skip number when there are ties?
Okay, this produces same output as the VBA I suggested:
SELECT Grades.RollNo, Grades.English, Grades.Math, Grades.Physics, [English]+[Math]+[Physics] AS Total,
(SELECT Count(*) FROM (SELECT DISTINCT [English]+[Math]+[Physics] AS Total FROM Grades) AS DistinctGrades
WHERE DistinctGrades.Total >= [English]+[Math]+[Physics]) AS Rank
FROM Grades
ORDER BY [English]+[Math]+[Physics] DESC;
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.