How can I rank in an access query?
Paul Mendez
like to rank these values from highest to lowest so that they will be
presented in a report right next to its values. Is there a way I can
do this? If so, how?
108.05%
102.82%
101.66%
101.54%
97.87%
96.59%
95.71%
93.04%
92.49%
92.27%
80.34%
Where the 108.05% would be one and 102.82% would be number 2 and so
on.
Please help, been trying to do this for more than two days.
Thanks for the help
Paul
Salad
Go to the menu in report design and select View/Sort&Group. Create a
row there for your percentage field. Select Descending.
You DO NOT SORT from the query when using an Access report. If you do,
expect weird results.
Big Time
to appear. You can delete the label if you want. Go into the properties of
the text box and in the control source type "=1" (no quotation marks).
Further down the list, you'll see an option called "Running Sum" and by
default it's said to No. Change it to "Over Group" and when you preview the
report, you will rank numbers in descending order. Assuming you have your
percentages ordered in descending fashion, the highest percentage will be 1,
followed by the next one which will be 2 etc...
Hope this helps.
Mike
"Paul Mendez" <pme...@thelyndco.com> wrote in message
news:99915b66.04061...@posting.google.com...
Deano
In Access 2000 you could view the report in design view, click View, Sorting
and Grouping, then choose the field and sort order.
Or you could amend the query, choose design view and edit the Sort field.
Deano
> Paul Mendez wrote:
>
>> I have a list or percentages in a query (listed below) and I would
>> like to rank these values from highest to lowest so that they will be
>> presented in a report right next to its values. Is there a way I can
>> do this? If so, how?
>> 108.05%
>> 102.82%
>> 101.66%
>> 101.54%
>> 97.87%
>> 96.59%
>> 95.71%
>> 93.04%
>> 92.49%
>> 92.27%
>> 80.34%
>> Where the 108.05% would be one and 102.82% would be number 2 and so
>> on.
>> Please help, been trying to do this for more than two days.
>> Thanks for the help
>> Paul
>
>
> You DO NOT SORT from the query when using an Access report. If you
> do, expect weird results.
I didn't know this. Is this true for any version of Access and does it
apply to sorting all data types?
almish
> I have a list or percentages in a query (listed below) and I would
> like to rank these values from highest to lowest so that they will be
> presented in a report right next to its values. Is there a way I can
> do this? If so, how?
> 108.05%
> 80.34%
> Where the 108.05% would be one and 102.82% would be number 2 and so
> on.
Perhaps you could use this as a template:
SELECT p.KeyPct
, (SELECT COUNT(*) FROM tblMiscPct WHERE KeyPct >= p.KeyPct) As Rank
FROM tblMiscPct as p
ORDER BY p.KeyPct DESC;
The subquery relates to the outter queries' table alias (p) counting
the number of values equal to or greater giving a ranking.
This works with numeric values not Text values - if you have to you
can use the CDbl() conversion and lose the percentage sign:
CDbl(Left("108.2%", Len("108.2%")-1))
i.e.
CDbl(Left([KeyPct], Len([KeyPct])-1))
YMMV
'--------------
'John Mishefske
'--------------
Salad
A query/table that is sorted in the rowsource is irrelevent. I don't
know about all versions but I'll assume so. I haven't read any posts
that mention one of the new features is that you don't need to sort
reports from the Sort/Group menu option.
James Fortune
Value(1) = 5.0
Value(2) = 6.0
Value(3) = 5.0
Value(4) = 4.0
Team(1) = "Team Alpha"
Team(2) = "Team Gamma"
Team(3) = "Team Phi"
Team(4) = "Team Rho"
MsgBox(CStr(Team(1)) & " " & Value(1) & " " & Ranking(1, Value(),
4)) should return:
Team Alpha 5.0 2nd (tie)
If the Value and Team arrays are filled in from a SQL string that
sorts descending then:
MsgBox(CStr(Team(intI)) & " " & Value(intI) & " " & Ranking(intI,
Value(), 4)) should return:
Team Gamma 6.0 1st
Team Alpha 5.0 2nd (tie)
Team Phi 5.0 2nd (tie)
Team Rho 4.0 4th
Note: The MaxIdx of 4 can be calculated in the function from the array
rather than being passed in if the arrays are ReDim'd to the correct
size before the function call. Also, the Space function can be used
to align the results.
Function Ranking (Indx As Integer, Array() As Single, MaxIdx As
Integer) As String
'Assumes highest value in Array() is 1st and Indices start at 1
Dim NumAbove As Integer
Dim NumTied As Integer
Dim Count As Integer
Dim strOut As String
NumAbove = 0
NumTied = -1 'Don't count Indx
For Count = 1 To MaxIdx
If Array(Count) = Array(Indx) Then
NumTied = NumTied + 1
ElseIf Array(Count) > Array(Indx) Then
NumAbove = NumAbove + 1
End If
Next Count
Select Case NumAbove
Case 0: strOut = "1st"
Case 1: strOut = "2nd"
Case 2: strOut = "3rd"
Case Else: strOut = CStr(NumAbove + 1) & "th"
End Select
If NumTied > 0 Then strOut = strOut & " (tie)"
Ranking = strOut
End Function
James A. Fortune