Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table (1 Viewer)

[essaytee]

I'm returning to some Access data manipulation and I've come across a situation I thought was possible, but can't figure it out. I have adopted a work-around (explained at the end), but I thought there is a less-convoluted way.

I have a table that records running times of runners for each week of running. I scrape the data from Strava. The fields are, Week Ending date, Runners Name, Max Distance, Longest Run, Elevation, Number of runs and pace. There is also a primary key. For each week there are about 60 - 70 records.

I want to produce reports each week against all the categories, in descending order (ascending order for pace). So for Max Distance, the first record will be the max distance, obviously. I want to also include the position number, against all the records. The position number is not a field in the table. Is there a way in SQL to dynamically create the position number and incremented by 1 for each record? The runner that is in position 1 for the max distance for the week, may not be in position 1 for the longest run for week, and likewise for the other categories.

My work-around is as follows. Use VBA. Queries already created for the various categories, filtered to the desired week ending date and sorted accordingly. Create a temporary table, that is the same as the base table but with an additional field, Position. That temporary table is populated with data from the query (via Recordset.AddNew process) and the Position field starts at 1 and incremented by 1 for each next record. The temporary table is then the record source of a report.

Is there a simpler way or another way?

 

[MajP]

You can do this with a Dcount , subquery, or self join. Here are some examples search "Ranking Queries in Access".

Microsoft Access tips:

How to rank records in Microsoft Access

allenbrowne.com

Use Access SQL to do a grouped ranking

How do I rank salespeople by # customers grouped by department (with ties included)? For example, given this table, I want to create the Rank column on the right. How should I do this in Access?

stackoverflow.com

There are lots of examples and techniques. If you show a snapshot of the table we can give a better solution.

 

[CJ_London]

If this is a report, that has a running sum function- just sum 1.

If not a report you can create a ‘row number’ function in a module to use in a query. I’m away from my computer right now but the function at its simplest is

function rownum(optional v as variant) as long
static r as long

if isnull(v) then
   r=0
 else
   r=r+1
 end if
 rownum=r
 end function

And the sql is something like this to generate the rows

Select name, place, rownum(place) as row
From mytable
Where rownum()=false

SQL may not be quite right, if you haven’t a solution by tomorrow I’ll provide an example

 

[essaytee]

I modified the argument list to set v = null, as follows:

Function rownum(Optional v As Variant = Null) As Long
    Static r As Long

    If IsNull(v) Then
        r = 0
    Else
        r = r + 1
        Debug.Print "v = " & v & "     --- and ---    r = " & Str(r)
    End If
    rownum = r
End Function

It works, sort of, but I'm finding that the first displayed record is 2 and then it increments by 1 after that. I added debug row, printing the value of v and r, the immediate window is as follows:

? rownum()
 0
v = Dennis Jones     --- and ---    r =  1
v = Dennis Jones     --- and ---    r =  2
v = Glynn Smith     --- and ---    r =  3
v = Wendy Green ©     --- and ---    r =  4
v = Greg Johnson (on IG/...     --- and ---    r =  5

I first ran rownum() without a parameter, to clear it back to zero. I then ran the query, and the output shows the first two records are in fact the same record. There is only one Dennis Jones record. The others follow on correctly.

What I also noticed, is that on running the query and leaving it open, background processes must be running as the immediate window is populated with fresh rownum, incremented by one, every so many seconds/minutes. Of course, if I click into a row the value of row is updated. I'd be using this method with an append type query so that should not be a factor.

 

[CJ_London]

The criteria should reset the r value to 0 and is only called once when you open the query.

Clicking on a row will ‘refresh’ the row number, there is a fix which I can see if I can dig out but using as an append query should also work.

See if this link helps - it uses variations of the function

Making a form look like a report

As a result of this post https://www.access-programmers.co.uk/forums/threads/emulate-reports-grouping-levels-with-forms.326697/ I thought I would diddle around and come up with some examples. This is a simple format - only shows the customer once and uses conditional formatting to change the...

www.access-programmers.co.uk

 

[arnelgp]

this function requires an Autonumber field to be passed"

'arnelgp
Public Function rankV2(ByVal id As Long) As Long
    Static sQueryName As String
    Dim sCurrentQuery As String
    Dim i As Long
    sCurrentQuery = Application.CurrentObjectName
    If sQueryName <> sCurrentQuery Then
        sQueryName = sCurrentQuery
    End If
    With CurrentDb.QueryDefs(sQueryName).OpenRecordset(dbOpenSnapshot)
        .FindFirst "id = " & id
        Do Until .BOF
            i = i + 1
            .MovePrevious
        Loop
        .Close
    End With
    rankV2 = i
End Function

sample usage:

SELECT Table1.ID, Table1.name, Table1.score, rankv2([id]) AS Rank 
FROM Table1
ORDER BY Table1.score DESC;

 

[CJ_London]

this is the actual function I use

Function RowNumber(Optional r As Variant = -1) As Variant
Static X As Long

    If r = -1 Then
    
        X = 0

    Else
    
        X = X + 1
        
    End If
    
    RowNumber = X
    
End Function

and the sql

SELECT *, RowNumber([PK]) AS RowNum
FROM myTable
WHERE RowNumber()=False

In a datasheet or form in datasheet view you do get the issue of the values updating when clicking on rows. However for a form in continuous view this does not happen. You will still get the issue if the form or query window is scrolled or resized, in all cases resolved by resorting the data (which could be triggered by the resize event, not sure about scrolling...)

I'm sure I developed a fix for this a number of years ago, but cannot now find it

Pretty sure Arnel's suggestion can be improved by making the recordset static (as well as the query name) so it doesn't have to be opened for every row (not tested)

static sRS as dao.recordset

    sCurrentQuery = Application.CurrentObjectName
    If sQueryName <> sCurrentQuery Then
        sQueryName = sCurrentQuery
        set sRS = CurrentDb.QueryDefs(sQueryName).OpenRecordset(dbOpenSnapshot)
    End If
    With sRS
    ...
    ...

 

[MarkK]

For those iterating over recordset rows to find rank, you might like the Recordset.AbsolutePosition property. This property is read/write. See also PercentPosition.

 

[essaytee]

For those iterating over recordset rows to find rank, you might like the Recordset.AbsolutePosition property. This property is read/write. See also PercentPosition.

Thanks for that, yes, now I can replace my counting variable for record number with the .AbsolutePosition + 1 property.

 

[essaytee]

this is the actual function I use

Function RowNumber(Optional r As Variant = -1) As Variant
Static X As Long

    If r = -1 Then
   
        X = 0

    Else
   
        X = X + 1
       
    End If
   
    RowNumber = X
   
End Function

and the sql

SELECT *, RowNumber([PK]) AS RowNum
FROM myTable
WHERE RowNumber()=False

... snip ...

I'm still having the same issue that the first record is being accessed twice, according to my debug window. For my particular purposes I don't require a where clause filter, but if employing this method, I'd have to first run the function to reset it back to zero and then call the query. At the moment I'm only dealing with a normal select query so as I can see the results, along with the debug window.

 

[MajP]

Sorry, I did not notice this was for a report. I was thinking query which is more difficult. A report has a no code solution so not sure why wasting time on the functions.

As @CJ_London said use the running sum. The running sum is easier and likely more efficient since the report writes row by row. You can then rank over the entire thing or all groups.

 

[essaytee]

Sorry, I did not notice this was for a report. I was thinking query which is more difficult. A report has a no code solution so not sure why wasting time on the functions.

As @CJ_London said use the running sum. The running sum is easier and likely more efficient since the report writes row by row. You can then rank over the entire thing or all groups.

View attachment 117890

Thanks