Adding Rank Values

[dnelson24]

I have a query that shows 7 stores rank by volume dollars, what I want to do is add a field that adds a rank value of 10 for the store with highest volume dollars, 8 with the second highest volume dollars, etc.....how do I create this field...or any other ideas???

Thanks

David

 

[jdegeorge]

Will there always be 7 stores? Also, with 7 stores, and ranks starting at 10 with decreasing increments of 2, you don't have enough ranks (10, 8, 6, 4, 2, 0).

Am I missing something?

Jim "Get it right the first time, that's the main thing..."

 

[dnelson24]

No you are right on! there will not always be 7 stores...that one district has 7, others have more or less. I am only considering the top positions acutally....10,8,6,4,2.....I am ranking volume 10 down to 2, Gross Profit down to 2, and 3 other measures from 10 down to 2, then I will have to figure out to add up the stores corresponding values to get that stores overall value in order to show "Store of the Month"? Highest value in all measures wins.


David

 

[dnelson24]

is there a way to autonumber when generating a make table query?? i can have the query sort volume descending and then autonumber the records 1 thru 7 and then assign a value of 10 to record#1 and 8 to record#2 and so on?

David

 

[JimConrad48188]

You could write your own rank function like the one below.

If used in a query, I suggest that you always use a "MakeTable" query, function calls can be strange when you are scrolling around in a "Select" query result table.

Note that you must call the function twice in your query. The first time like this (to set the initial value -- You don't have to display the field):

RankGenerator("Reset",12)

The second call must pass in a fieldname to the function even though the value of the field is never actually used. Otherwise Jet will only run the function once (as the above call) and use the same result over and over. Just make sure the value of the field you use will never contain the string "Reset".

RankGenerator(MyField, -2)

Function RankGenerator(SomeField As Variant, Increment As Long) As Long

Static HoldNextRank As Long

If SomeField = "Reset" Then
HoldNextRank = Increment
Else
HoldNextRank = HoldNextRank + Increment
End If

RankGenerator = HoldNextRank

End Function

 

[jdegeorge]

Access has a TopValue function you can use.

If there are 3 measures you'll need to create 3 Functions that you'll add in a Module, 3 TOPVALUE queries of your table with each one concentrating on a different field (gross profit, total sales, etc.), and 1 Macro to run it all.

To simplify this, I created a table called tblStores, with 3 fields: Store, GrossProfit, GrossProfit Rank.

Here are the table values I used:

GrossProfit Store GrossProfit Rank
125000 Chicago
10000 Boston
5000 New York
1000 Dallas
250 Denver
100 San Francisco

I created a TOP VALUE query on this table. Here's the SQL to make it easy to see:

SELECT TOP 5 tblStores.Store, tblStores.GrossProfit, tblStores.[GrossProfit Rank]
FROM tblStores
ORDER BY tblStores.GrossProfit DESC;

Notice the TOP 5. This is basically a SELECT query with TOP 5 added after the SELECT statement. You can make this any number you want.

Then, I created a function called TopProfit() in a module. Here's that code:

Function TopProfit()

Dim MyDB As Database
Dim MySet As Recordset
Dim I As Integer
Dim R As Integer

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("select * from [qryGrossProfit] order by [GrossProfit] DESC", DB_OPEN_DYNASET)

MySet.MoveLast
I = MySet.RecordCount
R = 12

If MySet.RecordCount > 0 Then
MySet.MoveFirst
For I = 1 To MySet.RecordCount
MySet.Edit
MySet![GrossProfit Rank] = (R - 2)
MySet.Update
MySet.MoveNext
R = R - 2
Next
End If

End Function

Lastly, I created a Macro with 1 Action set to RUNCODE and TOPPROFIT() in the Function Name line.

If you take the time to set up this sample table, query, module and macro, and you run the macro, you'll see how it works.

The result will add ranks to all but San Francisco the way you want it.

Then, if you want to track TOTAL SALES, there should be a field in THE SAME TABLE called TOTAL SALES, and you can add a field called TOTAL SALES RANK.

Create a new query called qryTotalSales and a new function in the module called TotalSales(), changing the necessary fields to match the new query. Add this Function to THE SAME Macro as the 2nd Action line and when you run this it will take care of both ranks.

Let me know if you have questions. Good luck.

Jim "Get it right the first time, that's the main thing..."

 

[dnelson24]

Thanks adegeorge!
I have tried this but am getting an error when running the macro....method or data member not found on the MySet.Edit line of the code....i am not at all familiar with VB and am not sure if i have modified the code for TOPPROFIT() as i should have?

Thanks
David

 

[jdegeorge]

What are the names of the fields in your TABLE and QUERY. And, paste the Function here.

I'll take a look.

Jim "Get it right the first time, that's the main thing..."

 

[dnelson24]

Query:

SELECT TOP 5 [Table 99].District, [Table 99].Store, [Table 99].Volume, Not Null AS [Voume Rank] INTO [MGO1Store-Volume Table]
FROM [Table 99]
WHERE ((([Table 99].Employee) Like "Z***[STORE TOTALS]***&quot)
GROUP BY [Table 99].District, [Table 99].Store, [Table 99].Volume, Not Null
HAVING ((([Table 99].District)="MG OREGON&quot)
ORDER BY [Table 99].Volume DESC;


Table output from above query:
District Store Volume Voume Rank
MG Oregon 82 $85,632.38
MG Oregon 86 $79,404.02
MG Oregon 81 $77,101.72
MG Oregon 85 $54,777.24
MG Oregon 83 $44,429.48


Module code:
Function TopProfit()

Dim MyDB As Database
Dim MySet As Recordset
Dim I As Integer
Dim R As Integer

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("select * from [qryMGO1Store-Volume] order by [Volume] DESC", DB_OPEN_DYNASET)

MySet.MoveLast
I = MySet.RecordCount
R = 12

If MySet.RecordCount > 0 Then
MySet.MoveFirst
For I = 1 To MySet.RecordCount
MySet.Edit
MySet![Volume Rank] = (R - 2)
MySet.Update
MySet.MoveNext
R = R - 2
Next
End If

End Function



David

 

[jdegeorge]

Your table has a field called "Voume Rank" but you're calling a field named "Volume Rank" in your Function.

Fix one or the other and you should work.

Just a little typo.

Jim "Get it right the first time, that's the main thing..."

 

[jdegeorge]

Hold on...your query is a MAKE TABLE query. It can only be a SELECT query. You have to get rid of the MAKE TABLE part for now. The function I wrote will update TABLE 99's field called VOLUME RANK.

Jim "Get it right the first time, that's the main thing..."

 

[dnelson24]

fixed! same error! highlight in vb is over the .Edit!
David

 

[jdegeorge]

Also, you have grouping in the query, that won't let you do the EDIT / UPDATEs needed.

If you know that the District must = "MG OREGON" you don't need to group, then the whole thing works.

Here's your new SQL code that will update TABLE 99 for the correct records:

SELECT TOP 5 [Table 99].District, [Table 99].Store, [Table 99].Volume, [Table 99].[Voume Rank]
FROM [Table 99]
WHERE ((([Table 99].District)="MG OREGON&quot AND (([Table 99].Employee) Like "Z***[STORE TOTALS]***&quot)
ORDER BY [Table 99].Volume DESC;

Jim "Get it right the first time, that's the main thing..."

 

[dnelson24]

Volume Rank: ""
Group By


could it be how am i defining the volume rank field, as above?

David

 

[jdegeorge]

I saw that also. You typed

Voume Rank: Not Null

in the query. If you wanted to see Voume Rank that was not null, you should put NOT NULL on a criteria line not in the FIELD line.

Even if you fix this you'll get an error on the OPEN DYNASET because it can't be a MAKE TABEL query. It has to be a SELECT query for this to work.

After you've assigned all your ranks, then you can make a table of the results or just another select query.

Give it a whirl.

Jim "Get it right the first time, that's the main thing..."

 

[dnelson24]

made the changes....everything should be in order but still getting the error on .Edit???

Thanks Again

David

 

[jdegeorge]

Okay. What's your email address. I've created a database with everything you need in it. I'll send it to you.

Jim "Get it right the first time, that's the main thing..."

 

[dnelson24]

david@sleepcountry.com


i owe ya one!