Re: 1st, 2nd, 3rd, etc

John Mishefske wrote:

salad wrote:

Peter Mitchell wrote:

Havong sorted records in a query how do I allocate
highest score 1st
second highest 2nd
third highest 3rd
etc

the purpose could be students results for an exam for example.

Hope someone can help, please.

Peter

This may/may not work for you. Let's say you have a test with socre values between 100 to 0. You could create a column in the query similar to:
Rank:Dcount("ID","TestResults","Score < " & [Score]) + 1

This counts the id field of TestResults for scores less than this record's score and increments by 1. So you should get 1,2,3...

The problem will occur when you have ties. 100, 98, 97, 97, 95. In this case, the second 97 will be 4. I might make a query that groups on the score.
Select Distinct Score From TestResults
and save it as query1. Then create another query similar to:
Select Score, _
Dcount("Score","Query1","Score < " & [Score]) + 1 As Rank _
From TestResults
and save it as Query2.

In the original query, add Query2 to the builder and link Score To Score and add the column Rank. Now both 97's will have 3, 95 will be 4.

This is untested aircode but it should work.


DCount() definitely works but is slow. A purely SQL query will run faster but requires a bit of SQL knowledge to write.

I wrote the following:
SQL1:
SELECT s.ID, (Select Count(*) From Table1 Where ID < s.ID) AS Rank
FROM Table1 As S;

SQL2:
SELECT ID, DCount("ID","Table1","ID <" & [ID]) AS Rank
FROM Table1;

Both presented the data instantly on a 10,000 record table. Where both bogged down was when I added sorting on the rank field.

What I did notice was that, without sorting, I could scroll through the Dcount query with no ill effects. With your subselect, scrolling through the pages was a chore. Towards the end of the file it would bog down...I'd drag the slider down and it would pop back up as it recalced the values...that didn't occur with the Dcount() SQL.

For the most part, I have found SubSelects to be dogs even if a person has a modicum of knowlege of SQL when it comes to Access and avoid them like the plague. I would sooner have Query1 called by Query2 and Query2 called by Query3 and execute Query3 to achieve instant speed. But that's my preference, and what makes programming unique...that it works is the most important aspect to consider prior to speed issues.

So check it out. Create a table with an autonumber field and populate it with about 10K record count. See if you notice the drag on the method you propose.

You'll also notice my method eliminated the tie rankings issue.


Here is a sample query:

SELECT
( SELECT COUNT(*) FROM Standings
WHERE Points >= s.Points) AS Rank
, s.[Team ID]
, s.TeamNumber
, s.Points
FROM
Standings AS s
ORDER BY
Points DESC;

The subquery counts records in the table that have a Points value equal to or greater than the current record. So the top point earner will only see one record that meets the criteria (the top point earner's own record) and is assigned a value of 1 as "Rank".

This is an example of a subquery that is a self-join (i.e. the table is joined to itself).

You still have the tie issue:

Rank Team ID Points
1 1 8
3 4 6
3 3 6
4 2 4

But there are ways to solve that to; the solution would depend on how you define the tie-breaker...

.

Relevant Pages

  • RE: Need Idiots guide to this please
    ... Well this SQL query: ... FROM Query1, Query2; ... So just create those three queries, go into SQL view, and paste that SQL, then the third query should do the job. ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)