How to create a ranking with a query 
Author Message
 How to create a ranking with a query

I have a query which lists store and bookings:

Store                Bookings
Sears                  10,000
JCPenney              5,000
Wal-Mart            100,000
Sniggles                   500
Home Depot           7,000

How do I get a query to rank them from high to low, returning a number:

Store                Bookings        BookingsRanking
Wal-Mart            100,000            1
Sears                  10,000             2
Home Depot           7,000            3  
JCPenney              5,000            4
Sniggles                   500            5

Then I wish to throw this data in a table.  

Thanks,

Matt



Mon, 05 Dec 2005 21:08:34 GMT  
 How to create a ranking with a query

Quote:

>I have a query which lists store and bookings:

>Store                Bookings
>Sears                  10,000
>JCPenney              5,000
>Wal-Mart            100,000
>Sniggles                   500
>Home Depot           7,000

>How do I get a query to rank them from high to low, returning a number:

>Store                Bookings        BookingsRanking
>Wal-Mart            100,000            1
>Sears                  10,000             2
>Home Depot           7,000            3  
>JCPenney              5,000            4
>Sniggles                   500            5

Use a subquery to calculate the rank, something like this:

SELECT T.Store, T.Bookings,
        (SELECT Count(*)
         FROM table AS X
         WHERE X.Bookings >= T.Bookings
        ) AS BookingsRanking
FROM table AS T

Watch out for ties.

--
Marsh
MVP [MS Access]



Mon, 05 Dec 2005 22:58:56 GMT  
 How to create a ranking with a query
This article describes three ways to rank records within a query:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> I have a query which lists store and bookings:

> Store                Bookings
> Sears                  10,000
> JCPenney              5,000
> Wal-Mart            100,000
> Sniggles                   500
> Home Depot           7,000

> How do I get a query to rank them from high to low, returning a number:

> Store                Bookings        BookingsRanking
> Wal-Mart            100,000            1
> Sears                  10,000             2
> Home Depot           7,000            3
> JCPenney              5,000            4
> Sniggles                   500            5

> Then I wish to throw this data in a table.

> Thanks,

> Matt



Mon, 05 Dec 2005 22:58:48 GMT  
 How to create a ranking with a query

Quote:
>This article describes three ways to rank records within a query:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

I tried number 2 here.  It's sort of odd that it gives different results every
time I run it.  A ranking number is always missing, (43, 44, 46, 47...) but not
the same one each time.  It takes about 1 minute to run but remains sticky for
another minute.  Since it is working from a query and not a table, I'll base it
on a table and see if it works better.  Any helpful hints would be appreciated.

Thanks to those two who answered.

Matt



Tue, 06 Dec 2005 01:53:12 GMT  
 How to create a ranking with a query
Matt, without checking the article again, does the missing number come when
there's a tie? For example, if two runners have exactly the same time in a
sprint, you have two firsts and no second.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> >This article describes three ways to rank records within a query:
> >http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

> I tried number 2 here.  It's sort of odd that it gives different results
every
> time I run it.  A ranking number is always missing, (43, 44, 46, 47...)
but not
> the same one each time.  It takes about 1 minute to run but remains sticky
for
> another minute.  Since it is working from a query and not a table, I'll
base it
> on a table and see if it works better.  Any helpful hints would be
appreciated.

> Thanks to those two who answered.

> Matt



Tue, 06 Dec 2005 10:29:44 GMT  
 How to create a ranking with a query

Quote:

>Matt, without checking the article again, does the missing number come when
>there's a tie? For example, if two runners have exactly the same time in a
>sprint, you have two firsts and no second.

No, they are not ties.  I find it particularly odd that when I run it again and
again, off the same unchanged data, it returns slightly different results,
always skipping some rank number.  It should return 101 records.  I just ran it
6 times.  Five times It returned 100 records only missing once rank 45, once
rank 48, once rank 49, once rank 40 and once rank 45.  On the last time,
getting tired, no doubt, it returned 96 records only, skipping ranks 43, 47,
63, 64 and 65 all at the same time.  There is no rhyme or reason why it skips
these stores.  

I then threw the data into a table and ran it off the table.  It works in a
flash, unlike the two minutes the above query takes, and always works
correctly.  The table is also made in about 3 seconds so this is the best way
to do it anyway.

Thanks,

Matt



Tue, 06 Dec 2005 22:48:19 GMT  
 How to create a ranking with a query
I think maybe you have 2 or more entries of the same values, in that case
you
may see rank 44, 44, 46,47 if you have 2 x 5000 values for exmaple.

ac.


Quote:
> >This article describes three ways to rank records within a query:
> >http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

> I tried number 2 here.  It's sort of odd that it gives different results
every
> time I run it.  A ranking number is always missing, (43, 44, 46, 47...)
but not
> the same one each time.  It takes about 1 minute to run but remains sticky
for
> another minute.  Since it is working from a query and not a table, I'll
base it
> on a table and see if it works better.  Any helpful hints would be
appreciated.

> Thanks to those two who answered.

> Matt



Wed, 14 Dec 2005 01:22:06 GMT  
 How to create a ranking with a query
I think there is an earlier post in this subject that refutes that.  There is
no reason for the skips.  I should have mentioned it wasn't that.  Sorry.

I solved the problem by making the thing work off a table (made by a maketable
query) instead of churning and churning for two minutes and spewing out bad
numbers.

There is something wrong with the code when the things takes awhile to perform.

When working from a table, which is how their sample works, the query ranks
them in a flash.

Matt

Quote:
>I think maybe you have 2 or more entries of the same values, in that case
>you
>may see rank 44, 44, 46,47 if you have 2 x 5000 values for exmaple.

>ac.



Wed, 14 Dec 2005 02:02:47 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. VBA codes to rank records in query

2. How do I rank a query to include ties

3. Ranking Search Results in VB6 to Access 2000 Query

4. Absolute rank newbie - creating a connection in vb6

5. How do I create a query then use data bound controls on the query

6. Create a routine that creates html for every query

7. create table query creates strange fields

8. create table query creates strange fields

9. Create Table Query does not Create Desired Datafields

10. how to i rank record?

11. Need code for automatic ranking scores !

12. Percentile ranking with large datasets

 

 
Powered by phpBB® Forum Software