By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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 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.

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 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...
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 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.
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.