Rank by velocity and percentage function

[Jeremiah31]

I have an access query where I have to import data into an excel sheet to sort and group information by it's velocity code and percentage. This is starting to take up majority of my day sometimes. I would like to have a function where it would rank percetages by the velocity code from lowest to highest values.

The velocity code has seven differeent code values:
$ -- Highest velocity code
A
B
C
D
E
F
G
H
U
N
R - lowest velocity code.

In my query, I find the percentage of fill-rate for each "part" by divding what was shipped against ordered.

Example of my data:

Part Velocity Ordered Shipped Fill-Rate
DW1234 $ 100 80 .80
DW2134 $ 100 90 .90
DW5668 A 100 75 .75
DW9876 A 100 50 .50

I when a function that would be able to rank by velocity code with percentages like so:

Part Velocity Ordered Shipped Fill-Rate Rank DW1234 $ 100 80 .80 1
DW2134 $ 100 90 .90 2
DW5668 A 100 75 .75 1
DW9876 A 100 50 .50 2

Could someone please help me out? This will save me a lot of time and let me create reports in access instead of having to always import the data into excel and create a report each and every time when the data is different.

Thanks

Jeremiah

 

[CaptainD]

To bad you have the "U" where it is, otherwise you could do a simple query with "Order By Velocity DESC

 

[genomon]

You could add a dummy field next to the velocity code, populate it with numbers 1 -12, and sort on that.

Knowledge is knowing a tomato is a fruit; Wisdom is not putting it in a fruit salad.

 

[CaptainD]

Playing with it I created another table called tRanking

Besides an ID it has sRankCode that matched you ranking characters and an iRankNumber that I assigned to each sRankcode 1 - 12 since you have 12 rankings.

I then ran this query and it does the calculation. The only difference is the iRankNumber for A is 11 and $ is 12 (Not 1 and 2)

SELECT tOrders.Part, tOrders.Velocity, tRanking.iRankNumber, tOrders.Ordered, tOrders.Shipped, [shipped]/[ordered] AS [Fill-Rate]
FROM tOrders INNER JOIN tRanking ON tOrders.Velocity = tRanking.sRankCode;

That returns this

Part	Velocity	iRankNumber	Ordered	Shipped	Fill-Rate
DW1234	$	12	100	80	0.8
DW1234	$	12	100	90	0.9
DW5668	A	11	100	75	0.75
DW9876	A	11	100	50	0.5