Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 01-20-2018, 06:13 AM   #1
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,271
Thanks: 115
Thanked 3,081 Times in 2,799 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
How to show rank order in queries

NOTE:
This is based on a thread I posted a few weeks ago at
https://www.access-programmers.co.uk...23&postcount=1
I've copied it to the code repository so its easier to find for future reference

Its easy to create a rank order in Access reports but not so easy in queries.
If you Google the topic you will find several methods of doing so
The standard solution involves the uses of subqueries.
For example, see http://allenbrowne.com/ranking.html

However, the following method using the Serialize function below is in my opinion far easier:

Code:
Public Function Serialize(qryname As String, KeyName As String, keyValue) As Long

On Error GoTo Err_Handler

    'used to create rank order for records in a query
    'add as query field
    'Example Serialize("qry1","field1",[field1])
    
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
    
    rst.FindFirst Application.BuildCriteria(KeyName, rst.Fields(KeyName).type, keyValue)
    
    Serialize = Nz(rst.AbsolutePosition, -1) + 1

    rst.Close
    Set rst = Nothing

Exit_Handler:
    Exit Function
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in Serialize procedure: " & Err.Description
    GoTo Exit_Handler
    
End Function
For example in this query, the function is used to create a TableID field which serves as the row number:

Code:
SELECT Serialize("qryJSONFileTables","TableName",[TableName]) AS [TableID], MSysObjects.Name AS TableName, qryJSONFileTableNames.FileID, qryJSONFileTableNames.FileNameFROM qryJSONFileTableNames INNER JOIN MSysObjects ON qryJSONFileTableNames.TableName = MSysObjects.Name
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;
Attached is a simple example database using student assessment marks for a fictitious school to illustrate its use

It has 2 tables - tblAssessmentMarks/tblAssessmentTypes

Various queries including three with rank order fields
a) qryY7ScAss1MarkRANK - puts Y7 Science students in rank order by mark (descending order) - as several students have the same mark, the rank values are repeated e.g. two are ranked 2 so the next is ranked 4



b) qryY7ScAss1PupilRANK - same data but this time ranked in descending mark order by PupilID - so no repeated values for rank



c) qryY7HiAvgAssMarksRANK - Y7 History average marks by assessment in rank order

Attached Images
File Type: png QueryTiedRANK.Example.PNG (33.9 KB, 346 views)
File Type: png QueryRANK.Example2.PNG (34.0 KB, 354 views)
File Type: png QueryRANK.Example3.PNG (11.8 KB, 342 views)
Attached Files
File Type: zip AssessmentRankQueries.zip (76.9 KB, 81 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline  
The Following User Says Thank You to isladogs For This Useful Post:
usm01 (06-30-2018)
Old 01-22-2018, 04:12 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,218
Thanks: 83
Thanked 458 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: How to show rank order in queries

Something about the field 'AssRank' stinks to high heaven.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline  
Old 06-09-2018, 01:04 PM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,271
Thanks: 115
Thanked 3,081 Times in 2,799 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to show rank order in queries

Frothy
Its all in your mind .....

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline  
Closed Thread

Tags
rank order in queries

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
TIP How to show rank order in a query isladogs Queries 7 12-31-2017 03:16 AM
update Rank Order field markqu@msn.com Modules & VBA 3 11-17-2008 08:10 PM
Changing the Rank Order of Records ddmcn Modules & VBA 9 05-23-2007 09:11 AM
Rank Order Manipulation g-richardson Reports 2 11-21-2006 11:57 AM
Rank registration events in asc time order gsrai31 Modules & VBA 3 10-04-2005 07:23 AM




All times are GMT -8. The time now is 10:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World