rank query results

[Alan]

I know this topic has been visited before. I followed the suggestions (and
my own variants) I found in the search results, but I can't quite get tha
ranking to work. I guess it's been too long since I dealt with sub-queries.

This is for a scoring database. Here's my setup. I have one table that
lists dog names and their owners with a key field called ID that is
auto-number. I have another table that lists different events, the event
location, and the number of dogs in each class (section) in that event. This
table also has a key field called EventID that is auto-number. There is a
3rd table that simply lists weight classes. The weight field is auto-number
from 1-160 (but it doesn't have to be auto-number), and the Class field is A,
B, C, D, or Open (D is 59 and down, C is 60-80, B is 81-100, A is 101-120,
Open is 121 and up). Finally, I have a fourth table that is a compilation of
all the results for the various events. This table has eventID, dogID (both
linked to their respective tables), Dog Weight (link to class table), times,
distances, other generic fields, and finish place. This table also has a key
field that is auto-number.

I have a query that searches the Results table for all of it's fields, dog
name and owner from the dog table, and add 3 calculated fields. The first is
if any bonus points are earned for that event, and the second is total points
earned for that event, and last is weight percentage. This query works fine.
It's long but here it is.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.[Dog Name], tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt,
tblResults.Time, tblResults.Final_Wt, tblResults.Dist, tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus,
IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON tblDogs.ID = tblResults.Dog_ID) ON tblEvents.Event_ID =
tblResults.Event_ID) ON tblClass.Weight = tblResults.Dog_Wt;

I have a second query searching the results of the first query. This one
groups by class, then adds the points for each dog, and displays in
descending order.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results
GROUP BY qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, qryAll_Results.ID;

This query also does exactly what I want it to.

I want to rank these in order by points, most points wins, keeping them
grouped by class. I've gotten them all to be in last place, or all to be in
first place.

Is this better done using VBA?

 

[Ken Snell \(MVP\)]

Perhaps this is what you seek -- this would be a third query that uses the
second query (which I will assume is named qryPoints):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryAll_Results AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;


--

Ken Snell
<MS ACCESS MVP>

I know this topic has been visited before. I followed the suggestions (and
my own variants) I found in the search results, but I can't quite get tha
ranking to work. I guess it's been too long since I dealt with
sub-queries.

This is for a scoring database. Here's my setup. I have one table that
lists dog names and their owners with a key field called ID that is
auto-number. I have another table that lists different events, the event
location, and the number of dogs in each class (section) in that event.
This
table also has a key field called EventID that is auto-number. There is a
3rd table that simply lists weight classes. The weight field is
auto-number
from 1-160 (but it doesn't have to be auto-number), and the Class field is
A,
B, C, D, or Open (D is 59 and down, C is 60-80, B is 81-100, A is 101-120,
Open is 121 and up). Finally, I have a fourth table that is a compilation
of
all the results for the various events. This table has eventID, dogID
(both
linked to their respective tables), Dog Weight (link to class table),
times,
distances, other generic fields, and finish place. This table also has a
key
field that is auto-number.

I have a query that searches the Results table for all of it's fields, dog
name and owner from the dog table, and add 3 calculated fields. The first
is
if any bonus points are earned for that event, and the second is total
points
earned for that event, and last is weight percentage. This query works
fine.
It's long but here it is.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.[Dog Name], tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt,
tblResults.Time, tblResults.Final_Wt, tblResults.Dist, tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus,
IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON tblDogs.ID = tblResults.Dog_ID) ON tblEvents.Event_ID =
tblResults.Event_ID) ON tblClass.Weight = tblResults.Dog_Wt;

I have a second query searching the results of the first query. This one
groups by class, then adds the points for each dog, and displays in
descending order.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results
GROUP BY qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, qryAll_Results.ID;

This query also does exactly what I want it to.

I want to rank these in order by points, most points wins, keeping them
grouped by class. I've gotten them all to be in last place, or all to be
in
first place.

Is this better done using VBA?

 

[Ken Snell \(MVP\)]

Oops... didn't properly change all the names:

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryPoints AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;
--

Ken Snell
<MS ACCESS MVP>

Perhaps this is what you seek -- this would be a third query that uses the
second query (which I will assume is named qryPoints):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryAll_Results AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;


--

Ken Snell
<MS ACCESS MVP>

I know this topic has been visited before. I followed the suggestions
(and
my own variants) I found in the search results, but I can't quite get tha
ranking to work. I guess it's been too long since I dealt with
sub-queries.

This is for a scoring database. Here's my setup. I have one table that
lists dog names and their owners with a key field called ID that is
auto-number. I have another table that lists different events, the event
location, and the number of dogs in each class (section) in that event.
This
table also has a key field called EventID that is auto-number. There is
a
3rd table that simply lists weight classes. The weight field is
auto-number
from 1-160 (but it doesn't have to be auto-number), and the Class field
is A,
B, C, D, or Open (D is 59 and down, C is 60-80, B is 81-100, A is
101-120,
Open is 121 and up). Finally, I have a fourth table that is a
compilation of
all the results for the various events. This table has eventID, dogID
(both
linked to their respective tables), Dog Weight (link to class table),
times,
distances, other generic fields, and finish place. This table also has a
key
field that is auto-number.

I have a query that searches the Results table for all of it's fields,
dog
name and owner from the dog table, and add 3 calculated fields. The
first is
if any bonus points are earned for that event, and the second is total
points
earned for that event, and last is weight percentage. This query works
fine.
It's long but here it is.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.[Dog Name], tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt,
tblResults.Time, tblResults.Final_Wt, tblResults.Dist, tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus,
IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON tblDogs.ID = tblResults.Dog_ID) ON tblEvents.Event_ID =
tblResults.Event_ID) ON tblClass.Weight = tblResults.Dog_Wt;

I have a second query searching the results of the first query. This one
groups by class, then adds the points for each dog, and displays in
descending order.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results
GROUP BY qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, qryAll_Results.ID;

This query also does exactly what I want it to.

I want to rank these in order by points, most points wins, keeping them
grouped by class. I've gotten them all to be in last place, or all to be
in
first place.

Is this better done using VBA?

 

[Tom Ellison]

Hello Ken!

I thought I'd ask about your use of <= in the subquery.

This would avoid adding 1 all the time, as I'm usually posting it. If you
use < here instead, the first rank is 0 not 1.

However, it has another effect. If there's a 3 way tie for first place,
then all 3 would be ranked 3 instead of 1. Instead of the ranking being 1,
1, 1, 4 it would be 3, 3, 3, 4. Perhaps either would be acceptable. It
certainly doesn't affect the ranking in terms of showing who is ahead of the
next.

In track, we called that a three-way tie for first place, not a 3 way tie
for 3rd place. Does this pay win, place, or show? I actually don't know
that myself. Not a horserace guy.

I expect you are aware of these differences. Any comment?

I wanted to ask for the benefit of those reading our posts, and to perhaps
straighten out my own thinking on it.

Your friend,
Tom Ellison

Oops... didn't properly change all the names:

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryPoints AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;
--

Ken Snell
<MS ACCESS MVP>

Perhaps this is what you seek -- this would be a third query that uses
the second query (which I will assume is named qryPoints):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryAll_Results AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;


--

Ken Snell
<MS ACCESS MVP>

I know this topic has been visited before. I followed the suggestions
(and
my own variants) I found in the search results, but I can't quite get
tha
ranking to work. I guess it's been too long since I dealt with
sub-queries.

This is for a scoring database. Here's my setup. I have one table that
lists dog names and their owners with a key field called ID that is
auto-number. I have another table that lists different events, the
event
location, and the number of dogs in each class (section) in that event.
This
table also has a key field called EventID that is auto-number. There is
a
3rd table that simply lists weight classes. The weight field is
auto-number
from 1-160 (but it doesn't have to be auto-number), and the Class field
is A,
B, C, D, or Open (D is 59 and down, C is 60-80, B is 81-100, A is
101-120,
Open is 121 and up). Finally, I have a fourth table that is a
compilation of
all the results for the various events. This table has eventID, dogID
(both
linked to their respective tables), Dog Weight (link to class table),
times,
distances, other generic fields, and finish place. This table also has
a key
field that is auto-number.

I have a query that searches the Results table for all of it's fields,
dog
name and owner from the dog table, and add 3 calculated fields. The
first is
if any bonus points are earned for that event, and the second is total
points
earned for that event, and last is weight percentage. This query works
fine.
It's long but here it is.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.[Dog Name], tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt,
tblResults.Time, tblResults.Final_Wt, tblResults.Dist, tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus,
IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON tblDogs.ID = tblResults.Dog_ID) ON tblEvents.Event_ID =
tblResults.Event_ID) ON tblClass.Weight = tblResults.Dog_Wt;

I have a second query searching the results of the first query. This
one
groups by class, then adds the points for each dog, and displays in
descending order.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results
GROUP BY qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, qryAll_Results.ID;

This query also does exactly what I want it to.

I want to rank these in order by points, most points wins, keeping them
grouped by class. I've gotten them all to be in last place, or all to
be in
first place.

Is this better done using VBA?

 

[Alan]

Ken,

With two little mods, this works great. Thanks!
I changed one of the joins to >= because it's most points wins, and changed
the order by.

Alan

Oops... didn't properly change all the names:

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryPoints AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;
--

Ken Snell
<MS ACCESS MVP>

Perhaps this is what you seek -- this would be a third query that uses the
second query (which I will assume is named qryPoints):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryAll_Results AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;


--

Ken Snell
<MS ACCESS MVP>

I know this topic has been visited before. I followed the suggestions
(and
my own variants) I found in the search results, but I can't quite get tha
ranking to work. I guess it's been too long since I dealt with
sub-queries.

This is for a scoring database. Here's my setup. I have one table that
lists dog names and their owners with a key field called ID that is
auto-number. I have another table that lists different events, the event
location, and the number of dogs in each class (section) in that event.
This
table also has a key field called EventID that is auto-number. There is
a
3rd table that simply lists weight classes. The weight field is
auto-number
from 1-160 (but it doesn't have to be auto-number), and the Class field
is A,
B, C, D, or Open (D is 59 and down, C is 60-80, B is 81-100, A is
101-120,
Open is 121 and up). Finally, I have a fourth table that is a
compilation of
all the results for the various events. This table has eventID, dogID
(both
linked to their respective tables), Dog Weight (link to class table),
times,
distances, other generic fields, and finish place. This table also has a
key
field that is auto-number.

I have a query that searches the Results table for all of it's fields,
dog
name and owner from the dog table, and add 3 calculated fields. The
first is
if any bonus points are earned for that event, and the second is total
points
earned for that event, and last is weight percentage. This query works
fine.
It's long but here it is.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.[Dog Name], tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt,
tblResults.Time, tblResults.Final_Wt, tblResults.Dist, tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus,
IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON tblDogs.ID = tblResults.Dog_ID) ON tblEvents.Event_ID =
tblResults.Event_ID) ON tblClass.Weight = tblResults.Dog_Wt;

I have a second query searching the results of the first query. This one
groups by class, then adds the points for each dog, and displays in
descending order.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results
GROUP BY qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, qryAll_Results.ID;

This query also does exactly what I want it to.

I want to rank these in order by points, most points wins, keeping them
grouped by class. I've gotten them all to be in last place, or all to be
in
first place.

Is this better done using VBA?

 

[Ken Snell \(MVP\)]

You are correct that the query I posted will not show a "1" for the ranking
if there is more than one entity with the same value that is being ranked.
Using the approach I posted is best if there would never be a tie for the
value. Otherwise, I concur with your thought about a different approach --
which would be (using my earlier example):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
((SELECT Count(*)
FROM qryPoints AS T
WHERE T.Class = Q.Class AND
T.Points < Q.Points) + 1) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;

--

Ken Snell
<MS ACCESS MVP>

Hello Ken!

I thought I'd ask about your use of <= in the subquery.

This would avoid adding 1 all the time, as I'm usually posting it. If you
use < here instead, the first rank is 0 not 1.

However, it has another effect. If there's a 3 way tie for first place,
then all 3 would be ranked 3 instead of 1. Instead of the ranking being
1, 1, 1, 4 it would be 3, 3, 3, 4. Perhaps either would be acceptable.
It certainly doesn't affect the ranking in terms of showing who is ahead
of the next.

In track, we called that a three-way tie for first place, not a 3 way tie
for 3rd place. Does this pay win, place, or show? I actually don't know
that myself. Not a horserace guy.

I expect you are aware of these differences. Any comment?

I wanted to ask for the benefit of those reading our posts, and to perhaps
straighten out my own thinking on it.

Your friend,
Tom Ellison

Oops... didn't properly change all the names:

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryPoints AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;
--

Ken Snell
<MS ACCESS MVP>

Perhaps this is what you seek -- this would be a third query that uses
the second query (which I will assume is named qryPoints):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryAll_Results AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;


--

Ken Snell
<MS ACCESS MVP>


I know this topic has been visited before. I followed the suggestions
(and
my own variants) I found in the search results, but I can't quite get
tha
ranking to work. I guess it's been too long since I dealt with
sub-queries.

This is for a scoring database. Here's my setup. I have one table
that
lists dog names and their owners with a key field called ID that is
auto-number. I have another table that lists different events, the
event
location, and the number of dogs in each class (section) in that event.
This
table also has a key field called EventID that is auto-number. There
is a
3rd table that simply lists weight classes. The weight field is
auto-number
from 1-160 (but it doesn't have to be auto-number), and the Class field
is A,
B, C, D, or Open (D is 59 and down, C is 60-80, B is 81-100, A is
101-120,
Open is 121 and up). Finally, I have a fourth table that is a
compilation of
all the results for the various events. This table has eventID, dogID
(both
linked to their respective tables), Dog Weight (link to class table),
times,
distances, other generic fields, and finish place. This table also has
a key
field that is auto-number.

I have a query that searches the Results table for all of it's fields,
dog
name and owner from the dog table, and add 3 calculated fields. The
first is
if any bonus points are earned for that event, and the second is total
points
earned for that event, and last is weight percentage. This query works
fine.
It's long but here it is.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.[Dog Name], tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt,
tblResults.Time, tblResults.Final_Wt, tblResults.Dist,
tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus,
IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON tblDogs.ID = tblResults.Dog_ID) ON tblEvents.Event_ID =
tblResults.Event_ID) ON tblClass.Weight = tblResults.Dog_Wt;

I have a second query searching the results of the first query. This
one
groups by class, then adds the points for each dog, and displays in
descending order.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results
GROUP BY qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, qryAll_Results.ID;

This query also does exactly what I want it to.

I want to rank these in order by points, most points wins, keeping them
grouped by class. I've gotten them all to be in last place, or all to
be in
first place.

Is this better done using VBA?

 

[Ken Snell \(MVP\)]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Ken,

With two little mods, this works great. Thanks!
I changed one of the joins to >= because it's most points wins, and
changed
the order by.

Alan

Oops... didn't properly change all the names:

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryPoints AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;
--

Ken Snell
<MS ACCESS MVP>

Perhaps this is what you seek -- this would be a third query that uses
the
second query (which I will assume is named qryPoints):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryAll_Results AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;


--

Ken Snell
<MS ACCESS MVP>


I know this topic has been visited before. I followed the suggestions
(and
my own variants) I found in the search results, but I can't quite get
tha
ranking to work. I guess it's been too long since I dealt with
sub-queries.

This is for a scoring database. Here's my setup. I have one table
that
lists dog names and their owners with a key field called ID that is
auto-number. I have another table that lists different events, the
event
location, and the number of dogs in each class (section) in that
event.
This
table also has a key field called EventID that is auto-number. There
is
a
3rd table that simply lists weight classes. The weight field is
auto-number
from 1-160 (but it doesn't have to be auto-number), and the Class
field
is A,
B, C, D, or Open (D is 59 and down, C is 60-80, B is 81-100, A is
101-120,
Open is 121 and up). Finally, I have a fourth table that is a
compilation of
all the results for the various events. This table has eventID, dogID
(both
linked to their respective tables), Dog Weight (link to class table),
times,
distances, other generic fields, and finish place. This table also
has a
key
field that is auto-number.

I have a query that searches the Results table for all of it's fields,
dog
name and owner from the dog table, and add 3 calculated fields. The
first is
if any bonus points are earned for that event, and the second is total
points
earned for that event, and last is weight percentage. This query
works
fine.
It's long but here it is.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.[Dog Name], tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt,
tblResults.Time, tblResults.Final_Wt, tblResults.Dist,
tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus,
IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON tblDogs.ID = tblResults.Dog_ID) ON tblEvents.Event_ID =
tblResults.Event_ID) ON tblClass.Weight = tblResults.Dog_Wt;

I have a second query searching the results of the first query. This
one
groups by class, then adds the points for each dog, and displays in
descending order.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results
GROUP BY qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, qryAll_Results.ID;

This query also does exactly what I want it to.

I want to rank these in order by points, most points wins, keeping
them
grouped by class. I've gotten them all to be in last place, or all to
be
in
first place.

Is this better done using VBA?

 

[Tom Ellison]

Dear Ken,

Thanks for your help with my "gut check" on this.

I'm wondering now about one of the finer points. You said, "the approach I
posted is best if there would never be a tie for the value." I was
considering what makes it better.

The processing to add 1 is pretty small. I would have thought the
processing to continue finding and counting just one more row would be more.
That processing is fastest if it uses an index. I find myself trying to
visualize how that works. An index lookup would be performed at the point
in qryPoints if it were a table at the correlated values in T.Class and
T.Points, assuming there is an index on this combination of columns. If
that index is specified as unique (agreeing with your "never a tie"
statement) an optimized query would know to stop. The + 1 would be
effectively be added, as your original post has performed. So, you might
have saved subtracting 1 and adding it back. Sounds good so far, but very
very minor, as in 10 microseconds.

If not indexed (as a Jet query would be) or even if indexed but not unique,
the query must proceed to check the next record, and the next, till a
non-match is found.

In the case it is indexed but not specified as unique, then this risks
passing to the next node of the index, involving additional disk access.
This is on the order of 10 milliseconds.

If not indexed, the table or query must be scanned to find all values in the
range. Not nice, but out of what may take several seconds, you will
probably have saved the 10 microsecnds.

Well, it looks to me like it's a pretty slender margin to look for. What I
like is to use a single method that works in all cases, and never has a
penalty more than microseconds. I've enjoyed trying to work this through.

Be seeing you, I hope!

Tom Ellison

You are correct that the query I posted will not show a "1" for the
ranking if there is more than one entity with the same value that is being
ranked. Using the approach I posted is best if there would never be a tie
for the value. Otherwise, I concur with your thought about a different
approach -- which would be (using my earlier example):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
((SELECT Count(*)
FROM qryPoints AS T
WHERE T.Class = Q.Class AND
T.Points < Q.Points) + 1) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;

--

Ken Snell
<MS ACCESS MVP>

Hello Ken!

I thought I'd ask about your use of <= in the subquery.

This would avoid adding 1 all the time, as I'm usually posting it. If
you use < here instead, the first rank is 0 not 1.

However, it has another effect. If there's a 3 way tie for first place,
then all 3 would be ranked 3 instead of 1. Instead of the ranking being
1, 1, 1, 4 it would be 3, 3, 3, 4. Perhaps either would be acceptable.
It certainly doesn't affect the ranking in terms of showing who is ahead
of the next.

In track, we called that a three-way tie for first place, not a 3 way tie
for 3rd place. Does this pay win, place, or show? I actually don't know
that myself. Not a horserace guy.

I expect you are aware of these differences. Any comment?

I wanted to ask for the benefit of those reading our posts, and to
perhaps straighten out my own thinking on it.

Your friend,
Tom Ellison

Oops... didn't properly change all the names:

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryPoints AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;
--

Ken Snell
<MS ACCESS MVP>

Perhaps this is what you seek -- this would be a third query that uses
the second query (which I will assume is named qryPoints):

SELECT Q.Class, Q.[Dog Name], Q.Owner, Q.Points,
(SELECT Count(*)
FROM qryAll_Results AS T
WHERE T.Class = Q.Class AND
T.Points <= Q.Points) AS ClassRanking
FROM qryPoints AS Q
ORDER BY Q.Class, Q.[Dog Name],
Q.Owner, Q.ID;


--

Ken Snell
<MS ACCESS MVP>


I know this topic has been visited before. I followed the suggestions
(and
my own variants) I found in the search results, but I can't quite get
tha
ranking to work. I guess it's been too long since I dealt with
sub-queries.

This is for a scoring database. Here's my setup. I have one table
that
lists dog names and their owners with a key field called ID that is
auto-number. I have another table that lists different events, the
event
location, and the number of dogs in each class (section) in that
event. This
table also has a key field called EventID that is auto-number. There
is a
3rd table that simply lists weight classes. The weight field is
auto-number
from 1-160 (but it doesn't have to be auto-number), and the Class
field is A,
B, C, D, or Open (D is 59 and down, C is 60-80, B is 81-100, A is
101-120,
Open is 121 and up). Finally, I have a fourth table that is a
compilation of
all the results for the various events. This table has eventID, dogID
(both
linked to their respective tables), Dog Weight (link to class table),
times,
distances, other generic fields, and finish place. This table also
has a key
field that is auto-number.

I have a query that searches the Results table for all of it's fields,
dog
name and owner from the dog table, and add 3 calculated fields. The
first is
if any bonus points are earned for that event, and the second is total
points
earned for that event, and last is weight percentage. This query
works fine.
It's long but here it is.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class,
tblDogs.[Dog Name], tblDogs.ID, tblDogs.Owner, tblDogs.Sex,
tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt,
tblResults.Time, tblResults.Final_Wt, tblResults.Dist,
tblResults.Place,
IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus,
IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus]))))))
AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN
tblResults ON tblDogs.ID = tblResults.Dog_ID) ON tblEvents.Event_ID =
tblResults.Event_ID) ON tblClass.Weight = tblResults.Dog_Wt;

I have a second query searching the results of the first query. This
one
groups by class, then adds the points for each dog, and displays in
descending order.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results
GROUP BY qryAll_Results.Class, qryAll_Results.[Dog Name],
qryAll_Results.Owner, qryAll_Results.ID;

This query also does exactly what I want it to.

I want to rank these in order by points, most points wins, keeping
them
grouped by class. I've gotten them all to be in last place, or all to
be in
first place.

Is this better done using VBA?

 

[Ken Snell \(MVP\)]

Dear Ken,

I'm wondering now about one of the finer points. You said, "the approach
I posted is best if there would never be a tie for the value." I was
considering what makes it better.

Word choice wasn't clear.... my intent was that the approach I suggested
works when there is no possible tie for the values. I have no disagreement
with your thoughts about using the "+ 1" approach as a general solution.

 

[Tom Ellison]

Dear Ken,

Friend, I hope you understand I was just trying to dig out any nugget of
understanding I might have missed.

Thanks for helping my understanding of what you posted. I appreciate it.

I try to listen and learn. In any case, I enjoy contact with my friends.

Tom Ellison

 

[Alan]

Tom,

Thanks for thinking of that placing delima. I have to account for that all
the time with my existing process (and in sailing results), not sure why I
didn't think of it here. I took the = off, added the +1, and it works great.

Thanks again both of you.

Alan

 

[Ken Snell \(MVP\)]

Dear Ken,

Friend, I hope you understand I was just trying to dig out any nugget of
understanding I might have missed.

Thanks for helping my understanding of what you posted. I appreciate it.

I try to listen and learn. In any case, I enjoy contact with my friends.

As do I... hoping that we meet up again in the future!