Add Ranking Number or RowNumber to GroupBy Query

[edmscan]

I would like to add a RANKING value to a query .. here is the result from the QUERY.

Essentially .. I would like to add an item "Ranking" or really just a "RowNumber" to show the order of the records .. ie Record#1 (highest CountOfGroup1, 990 in this case) would have ranking = 1 ... Record#2 (771 in this case) = 2.

The data is already sorted by CountOfGroup1 .. which is how I want the records ordered and the Ranking to be evaluated accordingly.

I have found solutions out there but I need something that will not mess up my group by query.



Here is my SQL ...

SELECT Kevin.User, Transmissions.Type, Transmissions.Group, KevinGroup.Name, Count(Transmissions.Group) AS CountOfGroup1, Transmissions.Radio
FROM Kevin RIGHT JOIN (Transmissions LEFT JOIN KevinGroup ON Transmissions.Group = KevinGroup.Group) ON Kevin.Radio = Transmissions.Radio
GROUP BY Kevin.User, Transmissions.Type, Transmissions.Group, KevinGroup.Name, Transmissions.Radio
ORDER BY Count(Transmissions.Group) DESC , Transmissions.Radio DESC;

[June7]

You want to rank the aggregate records? Build another query that uses the aggregate query as source.

What is purpose of this ranking value?

http://allenbrowne.com/ranking.html

Textbox in report has RunningSum property which can be used to show a sequential number.

[edmscan]

It is for displaying the records on a continuous form. including the ranking item ... if it was a report it may be easier.

[June7]

Try embedding a report on form. Since aggregate queries are not editable, a form is not necessary for displaying the data.

I still don't see what purpose this serves - the aggregate data is dynamic.

[edmscan]

I show the data on the continuous form .. and that part is good. But I want to show a ranking for each record .. ie the highest number of "CountOfGroup1" gets the best ranking .. and so on. I can get a function to work on the form .. but
I cannot seem to get it to calculate before the form opens (I am ok with doing it on the form .. as long as it is calculated before it actually opens) .. and thus the ranking data fills in as you scroll. I would prefer to have this calculated before the form opens .. ie in the query itself.

[June7]

What happens if you do the calc in query and bind form to that query? Do you get the same effect?

Ordering the records descending is not sufficient? Users can count the position.

Are users allowed to change the order of records on the form? What happens to the ranking?

[edmscan]

Using this function ... and it does work. But I would prefer to load up the control source before the form opens so that when I scroll it does not appear like it is loading the data.

I am calling it from the control source ... ie =RowNum([Forms]![frmTransmissions].[Form])

'Copyright Stephen Lebans 1999
'May not be resold
'Please include my 1 line Copyright notice
'in your code if you use these functions

'I left a bunch of development code in here in case anyone decides to go
'down the same paths I did.
'Created by Stephen Lebans with help from Chris Bergmans
' Updated by Allen Browne Oct/2002
'Production version of GetLineNumberForm
'Works in Form or SubForm mode
'Set controlsource of unbound Text box to
'= RowNum([Form])
'Type exactly as above

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
'Purpose: Numbering the rows on a form.
'Usage: Text box with ControlSource of: =RowNum([Form])

With frm.RecordsetClone
.Bookmark = frm.Bookmark
RowNum = .AbsolutePosition + 1
End With

Exit_RowNum:
Exit Function

Err_RowNum:
If Err.Number <> 3021& Then 'Ignore "No bookmark" at new row.
MsgBox "RowNum() error " & Err.Number & " - " & Err.Description
End If

RowNum = Null
Resume Exit_RowNum

End Function

[edmscan]

What happens if you do the calc in query and bind form to that query? Do you get the same effect?

Ordering the records descending is not sufficient? Users can count the position.

Are users allowed to change the order of records on the form? What happens to the ranking?

I would prefer if I could do it in the query .. even making another query based on the 1st one.

The form is just to display the data all the fields are locked and non enabled .. it is not modifiable at all. I am the only user.

[June7]

Did you review the Allen Browne link? He shows a query method. No idea how it will behave as form RecordSource.

Report not feasible alternative?

I've never had need for ranking.

[edmscan]

I will admit that I do not know how to modify the query above .. to add a row number.

I do have the function above working as I would like .. but I would prefer to call the function "before the form opens" .. so that the form is all filled in with the rownumber values when it opens.
Right now .. it displays .. but there is a delay for those values to show. Just not quite what I want.

Is there a better place to call that function ... ie on load ? on query ? I will try it out.

I did look at the link above .. and it seems the same as my issue right now. It is slow to display those numbers at run time.

[June7]

I have explored this before with other posters and the code has always run slow. In fact, in very large datasets it is virtually at a standstill.

[edmscan]

It is working sufficiently well for me .. I am fine with it. I have about 6800 records right now that are returned by the query (and this will only get bigger over time).

However .. I am really fine with the top 100 records in the continuous form but cannot find how to only show that number of records. Things will just get out of control if I do not limit it.

[June7]

Maybe SELECT TOP 100 ... will work for you.

[edmscan]

Select top 100 .. perfect.

Thanks

[edmscan]

I actually fixed that annoying updating of the row numbers on the form .. just added a delay of 1 second to the form loading, giving it time to load those numbers before the form actually displays.

Thanks for marking this solved ... and yes, I think we are sufficiently happy on this one.