Re: Ranking - dealing with ties

Sophie wrote:
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.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • RE: Field aliases not being returned properly in A2007
    ... I was wondering if you found a fix for the alias ... Sharepoint Lists as my data repository for about 3 months now. ... This has totally delayed the project I'm working on, I need this query to ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... If the query was "within" SQL Server, then alias ... gets treated differently than in a Jet query. ...
    (microsoft.public.access.queries)
  • Trying to find a maximum value amongst 3 columns in a dB row
    ... The column prefix 'a1' does not match with a table name or alias name ... used in the query. ... Either the table is not specified in the FROM clause ...
    (comp.databases.sybase)
  • RE: Double Trouble with Alias Columns & Nested Case When in APD/SQL Da
    ... Your best bet is to not attempt to use the alias in the same query that ... If the SQL ... How to reference an Alias column in the same query as that Alias ... How then do I nest Case When statements: ...
    (microsoft.public.access.queries)
  • Re: Help with DSum to create running sum
    ... You'll have to alias the table Activity as A in the main query for this ... DSum() function, 'cuz it returns a string that will be compared to ... then change the Alias property from the table name to it's Alias - A." ...
    (microsoft.public.access.queries)