Ranking Query For Multiple Groups using percentages

[JB]

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

 

[KARL DEWEY]

Create a table Velocity_Rank with field Rank like this ---
Velocity Rank
$ 1
A 2
B 3
C 4
D 5
E 6
F 7
G 8
H 9
U 10
N 11
R 12
Then use two queries ----
JB_1 ---
SELECT JB.Part, JB.Velocity, JB.Ordered, JB.Shipped, JB.[Fill-Rate],
Velocity_Rank.Rank
FROM JB INNER JOIN Velocity_Rank ON JB.Velocity = Velocity_Rank.Velocity;

SELECT Q.Part, Q.Velocity, Q.Ordered, Q.Shipped, Q.[Fill-Rate], (SELECT
COUNT(*) FROM JB_1 Q1
WHERE Q1.[Rank] = Q.[Rank]
AND Q1.[Fill-Rate] <= Q.[Fill-Rate]) AS Per_Rank
FROM JB_1 AS Q
ORDER BY Q.Rank, Q.[Fill-Rate];