424,483 Members | 1,464 Online
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

# 1st, 2nd, 3rd, etc

 P: n/a 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 Jul 11 '06 #1
6 Replies

 P: n/a 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. Jul 11 '06 #2

 P: n/a salad wrote: Peter Mitchell wrote: >Havong sorted records in a query how do I allocatehighest score 1stsecond highest 2ndthird highest 3rdetcthe 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. 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... -- '--------------- 'John Mishefske '--------------- Jul 13 '06 #3

 P: n/a John Mishefske wrote: salad wrote: >Peter Mitchell wrote: >>Havong sorted records in a query how do I allocatehighest score 1stsecond highest 2ndthird highest 3rdetcthe 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 socrevalues between 100 to 0. You could create a column in the querysimilar to: Rank:Dcount("ID","TestResults","Score < " & [Score]) + 1This counts the id field of TestResults for scores less than thisrecord'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. Inthis case, the second 97 will be 4. I might make a query that groupson the score. Select Distinct Score From TestResultsand save it as query1. Then create another query similar to: Select Score, _ Dcount("Score","Query1","Score < " & [Score]) + 1 As Rank _ From TestResultsand save it as Query2.In the original query, add Query2 to the builder and link Score ToScore 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... Jul 13 '06 #4

 P: n/a salad wrote: 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. Thanks, I'll definitely check that out when I have some time. It really surprises me that the DCount() performs that well on open. I've seen the update issue you pointed out as well. There are definitely times (especially with large recordsets 100,000) when I've seen SQL outperformed by VBA code. -- '--------------- 'John Mishefske '--------------- Jul 14 '06 #5

 P: n/a John Mishefske wrote: salad wrote: >I wrote the following:SQL1:SELECT s.ID, (Select Count(*) From Table1 Where ID < s.ID) AS RankFROM Table1 As S;SQL2:SELECT ID, DCount("ID","Table1","ID <" & [ID]) AS RankFROM Table1;Both presented the data instantly on a 10,000 record table. Whereboth bogged down was when I added sorting on the rank field.What I did notice was that, without sorting, I could scroll throughthe Dcount query with no ill effects. With your subselect, scrollingthrough the pages was a chore. Towards the end of the file it wouldbog down...I'd drag the slider down and it would pop back up as itrecalced the values...that didn't occur with the Dcount() SQL.For the most part, I have found SubSelects to be dogs even if a personhas a modicum of knowlege of SQL when it comes to Access and avoidthem like the plague. I would sooner have Query1 called by Query2 andQuery2 called by Query3 and execute Query3 to achieve instant speed.But that's my preference, and what makes programming unique...that itworks is the most important aspect to consider prior to speed issues.So check it out. Create a table with an autonumber field and populateit with about 10K record count. See if you notice the drag on themethod you propose.You'll also notice my method eliminated the tie rankings issue. Thanks, I'll definitely check that out when I have some time. It really surprises me that the DCount() performs that well on open. I've seen the update issue you pointed out as well. In my example I am opening a query that presents the data as a datasheet, not as a recordset. If you have a medium sized file like the one I used, as you page down the screen gets refreshed. Let's say that datasheet presents 25 lines. It only has to refresh 25 lines so the speed is quick on the dcount(). You'll notice it will paint all of the regular columns instantly and then the calced columns will be refreshed. That's OK by me. The drag towards the end of the file with the subselect would annoy me. I think it's Stephen Lebans that has some code that replaces the MS supplied Dcount() that's supposed to be faster. There are definitely times (especially with large recordsets 100,000) when I've seen SQL outperformed by VBA code. Both of our methods sucked when it came to pressing the AZ sort buttons. But no matter what, ranking records is a more time consuming practice than most SQL statements and the time delay is to be expected. Jul 14 '06 #6

 P: n/a salad wrote: >salad wrote: I think it's Stephen Lebans that has some code that replaces the MS supplied Dcount() that's supposed to be faster. Stephen may have but I base mine on Trevor Best's Domain Replacement functions: http://easyweb.easynet.co.uk/~trevor/AccFAQ/ >There are definitely times (especially with large recordsets >100,000) when I've seen SQL outperformed by VBA code. Both of our methods sucked when it came to pressing the AZ sort buttons. But no matter what, ranking records is a more time consuming practice than most SQL statements and the time delay is to be expected. Yes. At least if your goal is a report then its a bit easy to do most ranking in the report. If you are ranking large number of records and want them sortable then I think I'd look at using temp tables ala Tony Toews http://www.granite.ab.ca/access/temptables.htm I've used this before for a different reason and it found it performed well. -- '--------------- 'John Mishefske '--------------- Jul 14 '06 #7

### This discussion thread is closed

Replies have been disabled for this discussion.