Results 1 to 12 of 12
  1. #1
    printsol is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2021
    Posts
    33

    rank and total in single query

    hi
    i need single query to get total marks and rank on base of total marks
    Attached Thumbnails Attached Thumbnails table.jpg  

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  3. #3
    printsol is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2021
    Posts
    33
    same like attached image except total and rank column

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    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

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Maybe something along these lines would work for you, have a look at the qryRankWithTiesOneQuery query.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have a look at this article on my website: Rank order in queries
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    printsol is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2021
    Posts
    33
    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

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider:
    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
    Call the function in query:
    SELECT Grades.RollNo, [English]+[Math]+[Physics] AS Total, Rank([English]+[Math]+[Physics]) AS Rank
    FROM Grades
    ORDER BY Rank([English]+[Math]+[Physics]);

    Result:
    RollNo Total Rank
    4 24 1
    5 23 2
    3 23 2
    1 20 3
    2 18 4
    6 17 5
    If you really want ranking reversed, change the >= operator to <= in the function.

    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.

  10. #10
    printsol is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2021
    Posts
    33
    is there any way to do it with query with our vb

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have you looked at my link in post #7?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-12-2017, 03:09 AM
  2. Replies: 2
    Last Post: 08-23-2016, 11:58 AM
  3. Average Rank query
    By gcgoode in forum Access
    Replies: 11
    Last Post: 09-23-2015, 08:49 AM
  4. Rank Query
    By cbritt927 in forum Queries
    Replies: 2
    Last Post: 06-02-2015, 04:42 PM
  5. Query Rank
    By DHIGHTO in forum Queries
    Replies: 14
    Last Post: 01-12-2015, 02:59 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums