Ranking Prices 2

[KimVong]

I have a query and I would like to insert a field to this query that will give me a ranking from the highest price to the lowest, can anyone help me?

table is as follow

desc price ranking
A 10 1
B 8 2
C 5 3

How can I insert the ranking field into this table so that it will automatically ranking my prices.

any help is greatly appreciate

 

[GingerR]

sorry but a bit confused. you have a table with a field called Ranking, which has an integer in it? your question is that you want to insert a field into the table to show the ranking? looks like you already have that?
if what you mean is how can you see the data sorted with the highest ranking first, you can write a query for that.
make a query based on your table.
bring down all the field.
in the Ranking column, in the query grid where it says SORT, choose Descending. run the query (the red exclamation mark at the top of the screen). is this what you are looking for? if not, please explain further.

 

[KimVong]

sorry,
I was looking how to insert the ranking field into the table above.

original table look like this

Desc price
A 10
B 8
C 6

and this is the new table with a rank field I want to know how to do

Desc price rank
A 10 1
B 8 2
C 6 3

 

[GingerR]

go into the design view of the table.
type in Ranking and set the data type to Number.
close and save the table.

 

[GingerR]

oh--nevermind what i said before. now i understand.

questions: is this something you want to do on a regular basis? or just one time only?

 

[KimVong]

regular basis

 

[GingerR]

does it have to have this 'ranking' number, or are you using it just for sorting? what are you using it for?
sorry for the questions but i'm just trying to determine if i think you really should put this directly into the table, or if there is another solution that keeps the calculation dynamic. for instance, every time you add data to the table, you will have to re-run code that calculates the Ranking for every single row. it won't automatically happen.

 

[KimVong]

there is a ranking query that you can write but I just don't know how
and it should happend automatically
just want to have the ranking field so that user can view which one is one and which one is not.
that's all

 

[GingerR]

isnt the highest dollar amount always going to be Rank #1?
then you can write a query that sorts DESCENDING on the table. do you know how to do that?

 

[KimVong]

yes I do, but I want to be able to identify it by field, if it's the highese, the rank field will put a 1 and if it is second highest it will put a 2.

 

[GingerR]

how do you want this information displayed to the user? on a report? in a message box?
how many rows (ranks) will you want to show them? just the top two? or many? or all?

 

[GingerR]

put a field in your table called Ranking.
in this example, you will have to substitute TableName for whatever your table name is:

make a new module.
paste this in.
then put your cursor before the word Function and hit RUN:

Function GetRank()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * from TableName Order By Price Desc")
Dim i As Integer
i = 1
While Not (rs.EOF)
    rs.Edit
    rs!ranking = i
    rs.Update
    
    rs.MoveNext
    i = i + 1
Wend
rs.Close
End Function

it will fill in your table.

 

[KimVong]

GingerR
I tried the function you told me to, it's giving me an error on rs.edit, what do I need to do on my module so that it will take the rs.edit? I got this error saying, method or Data member not found

 

[GingerR]

paste you vba code here.
did you change the variable names to your own table and field names?

also, when it errors and shows you the code with the yellow stripe on it, you can glide your cursor over the variables and see how access is interpreting them.

 

[OU18]

Gnger I get the same error. Here is the code I used

Function GetRank()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * from tblSteve ")
Dim i As Integer
i = 1
While Not (rs.EOF)
rs.Edit
rs!ranking = i
rs.Update

rs.MoveNext
i = i + 1
Wend
rs.Close
End Function

 

[PHV]

Why storing a calculated value that can vary with time ?
Use a ranking query to always get the accurate value:
SELECT A.Desc, A.Price, Count(*) AS Rank
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Price <= B.Price
GROUP BY A.Desc, A.Price;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OU18]

PHV,
In my case, the rank only changes once a month. The prices are good for an entire month.

Also, I have tried the above it runs terribly slow. My table is some 5.2 million records.

Originally my table had 401,000 rows with each vendor in its own column. What I was truly trying to accomplish was to rank each row across multiple columns. Similar to Excel's Rank Function. But I have not been able to find anyway to do it. I was given advice by many people to normalize the data, to which I did via a union query. I then used that to make a table with all the Vendors, Regions and Prices.
I tried the above Ranking method but it takes far far to long for it to complete. The reason I actually need a Ranking Number is for routing in a switch. It needs a number to key off of. See I can have up to ten routes per region and we base our routing on rate. So we route the least cost rate first, then the second least cost rate and so on.

I saw the posting above and figured I would give it a try being the other example of using the query takes forever to run. And I am running a 3ghz machine with plenty of ram. And the database is located on the machine.

Any thoughts, suggestions are more then welcome

 

[PHV]

BTW, to avoid the error on rs.Edit, replace this:
Dim rs As Recordset
By this:
Dim rs As DAO.Recordset

The Microsoft DAO 3.x Library must be checked in the menu Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OU18]

Okay,
That worked great but one small problem. It just created a number from 1 to X regardless of region id. Is there a way to modify the function so that it will number 1 to x for each region based on region id.

Thanks for the help

 

[PHV]

Any chance you could post your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886