Ranking - dealing with ties

[Sophie]

I have a query that ranks items 'generously' (1,2,2,3 NOT 1,2,2,4) All
works perfectly, but I've been asked to modify results when printing reports
or displaying forms to append a T to the tied items. Like this...

Division Rank
D 1
B 2T
E 2T
A 3

I've tried a number of things so far with no success. Any good ideas out
there?

much thanks

 

[Marshall Barton]

I have a query that ranks items 'generously' (1,2,2,3 NOT 1,2,2,4) All
works perfectly, but I've been asked to modify results when printing reports
or displaying forms to append a T to the tied items. Like this...

Division Rank
D 1
B 2T
E 2T
A 3

It would help if you'd post a Copy/Paste of the query you
are using.

Without that, all I can say is that you should add another
calculated field something like:

IIf((SELECT Count(*) FROM table As V
WHERE V.score= T.score) > 1, "T", "") As Tie

 

[Sophie]

Hello Marshall, and thanks for the quick reply

Sorry, I should have given a few more details:

There are 3 queries: qryScores, qryDistinctScores, qryRankScores

qryRankScores has 3 fields:
1) Division (based on qryScore, Alias Scor1)
2) Score (also based on Scor1)
3) Rank: (Select Count (*) from qryDistinctScore _
Where [Score] > [Scor1].[Score])+1

This gives perfect rankings 1,2,2,3.. but I just need a way to append
those darn T's to the tied values.

If I may be allowed a follow up question - I really don't understand why an
Alias (Scor1) has to be used instead of the actual query name, qryScore.

 

[Marshall Barton]

There are 3 queries: qryScores, qryDistinctScores, qryRankScores

qryRankScores has 3 fields:
1) Division (based on qryScore, Alias Scor1)
2) Score (also based on Scor1)
3) Rank: (Select Count (*) from qryDistinctScore _
Where [Score] > [Scor1].[Score])+1

This gives perfect rankings 1,2,2,3.. but I just need a way to append
those darn T's to the tied values.

If I may be allowed a follow up question - I really don't understand why an
Alias (Scor1) has to be used instead of the actual query name, qryScore.

When posting about a query, use Copy/Paste of the query's
SQL View. Anything else can be ambiguous and/or incomplete.
In this case, I can't figure out why the Where clause has
the +1. Maybe it has something to do with how qryScores was
put togeteher so you should also post this query.

I really need to figure out that +1 before I can be sure the
kind of calculated field I posted earlier will work (even
after correcting the missing right parenthesis).

Your question about alias is also confusing without seeing
the full SQL statement, but I think the reason is because
the subquery and the main query are both drawing from the
same data source. One of them must use an alias so you can
indicate which data source a field belongs to. Your Where
clause above lets Access guess (not a good thing to do)
which of the two data sources it shoud use for the
unqualified Score field.

Note that many experienced query writers often alias every
table/query in a From clause, if for no other reason, just
to make the names shorter and reduce the clutter in all the
field references.

 

[Sophie]

Marshall

a) I didn't think until too late just to post the SQL - sorry. In any case,
I was able to use your post of Feb 17 to solve this problem. The SQL shown
below gives perfect results every time after numerous tests. FWIT, the '+1'
you asked about ensures the results look like 1,2T,2T,3 etc instead of
0,1T,1T,2...

qryDivScore has fields Score (Desc) and Div (Asc).
qryDistinctDivScore has field Score (Desc), with the Unique Value property
set.

I'm mildy surprised that this actually works because I had to refer to
qryDivScore using two different Aliases (S and T). I'm not sure if there is
a better way.

SELECT S.Div, S.Score, (SELECT Count(*) FROM [qryDistinctDivScore] AS D
WHERE [D].[Score] > .[Score])+1 AS SRank, IIf((SELECT Count(*) FROM
[qryDivScore] AS T WHERE T.Score = S.Score )>1,"T","") AS Tie, [SRank] &
[Tie] AS STRank
FROM [qryDivScore] AS S;

b) Your comments re Alias make a lot of sense. (removing ambiguity etc.)
--
Thanks
Sophie

There are 3 queries: qryScores, qryDistinctScores, qryRankScores

qryRankScores has 3 fields:
1) Division (based on qryScore, Alias Scor1)
2) Score (also based on Scor1)
3) Rank: (Select Count (*) from qryDistinctScore _
Where [Score] > [Scor1].[Score])+1

This gives perfect rankings 1,2,2,3.. but I just need a way to append
those darn T's to the tied values.

If I may be allowed a follow up question - I really don't understand why an
Alias (Scor1) has to be used instead of the actual query name, qryScore.

When posting about a query, use Copy/Paste of the query's
SQL View. Anything else can be ambiguous and/or incomplete.
In this case, I can't figure out why the Where clause has
the +1. Maybe it has something to do with how qryScores was
put togeteher so you should also post this query.

I really need to figure out that +1 before I can be sure the
kind of calculated field I posted earlier will work (even
after correcting the missing right parenthesis).

Your question about alias is also confusing without seeing
the full SQL statement, but I think the reason is because
the subquery and the main query are both drawing from the
same data source. One of them must use an alias so you can
indicate which data source a field belongs to. Your Where
clause above lets Access guess (not a good thing to do)
which of the two data sources it shoud use for the
unqualified Score field.

Note that many experienced query writers often alias every
table/query in a From clause, if for no other reason, just
to make the names shorter and reduce the clutter in all the
field references.

 

[Marshall Barton]

a) I didn't think until too late just to post the SQL - sorry. In any case,
I was able to use your post of Feb 17 to solve this problem. The SQL shown
below gives perfect results every time after numerous tests. FWIT, the '+1'
you asked about ensures the results look like 1,2T,2T,3 etc instead of
0,1T,1T,2...

qryDivScore has fields Score (Desc) and Div (Asc).
qryDistinctDivScore has field Score (Desc), with the Unique Value property
set.

I'm mildy surprised that this actually works because I had to refer to
qryDivScore using two different Aliases (S and T). I'm not sure if there is
a better way.

SELECT S.Div, S.Score, (SELECT Count(*) FROM [qryDistinctDivScore] AS D
WHERE [D].[Score] > .[Score])+1 AS SRank, IIf((SELECT Count(*) FROM
[qryDivScore] AS T WHERE T.Score = S.Score )>1,"T","") AS Tie, [SRank] &
[Tie] AS STRank
FROM [qryDivScore] AS S;

b) Your comments re Alias make a lot of sense. (removing ambiguity etc.)

I think I miss counted parenthesis (again), the +1 is clear
to me now.

It's nice that you sorted it out from so few (and
misleading) clues. Way to go!