Question on Ranking and Autonumbers (I think)

[mavver]

Hello All

Firstly I have been reading this forum for a while picking up hints and tips and am very grateful so big grats and thanks to all you guys for the help

The problem I am stuck on at the moment will hopfully be quite simple, but I should point out I have absolutely no skill or knowlede in Modules and VB language I most of my "stuff" via statements within the Design view.

Ok so here we go. I have a query which brings back the top 50 performing sales items, which is done by sorting them in descending order and using that filter thing at the top of the design view, which is set to 50. What I would like to do is insert a field in the query which gives the rank, so the best seller is given number 1, the second best 2 and so on and so forth.

Sorry if there is a topic already, I have tried to search, but couldnt find what I was looking for.

Ta in advance for any help

Mav

 

[gavinjb]

Hi,

As I dont know the structure of your tables, I can only make a guess, but if you have a unique column in your query you should be able to modify the following query to do what you want

SELECT TOP 50 T.Column1, T.Column2, (SELECT COUNT(*)+1 FROM Sheet1 T1 WHERE T1.ID < T.ID) AS MyCount
FROM Sheet1 T;

Gavin,

 

[gavinjb]

For more detailed info, have a look at the following link

http://www.pcreview.co.uk/forums/thread-1163742.php

 

[mavver]

Firstly thanks loads for the reply, I appreciate it

To be honest though that went way over my head (I really should do a SQL or VB course or something). I will try to describe the problem a little clearer with tables names and column names and the like

I have a database that is looking at UK regions levels of deprivation.

There are 150 regions and they have a "score" which ranges from 10,000 to 99,999, and I want to look at this data and bring back the 50 most deprived regions. So I built a query called "qryRanking_IMD" that look in the various tables and pulls back three columns

Region Code (a unique 3 digit code)
Region Name (a text based field with the Regions name in it)
IMD Score (the depravity score for each Region - that ranges from 10,000 to 99,999). This score has many many decimal places so it is possible that each of them are unique, however, the slightest chance exists that two could be the same.

Within this query I sorted IMD Score in descending order and used the Top Value figure to bring back the top 50 regions.

What I would like to do is to assign each of these regions a number ranging from 1 to 50.

Ta

Mav

 

[gavinjb]

Hi,

If I understand you correctly, you need an additional column which is a simple count, so the first record in your top 50 would have a value of 1, the send 2 and so on all the way down to 50.

If this is correct, the easiest way is to use the sample above (but you will need an unique sequential number field)

If you need some more help pm me with the select query and I will send you an updated version that should work.