Need help w/ creating module/function

[Tom]

Hi,

Currently, I use a function like below (between ****s).


********************************************************************
Public Function PriorityPoints(Priority As Variant) As Variant

Select Case Priority

Case "A": PriorityPoints = "0.60"
Case "B": PriorityPoints = "0.25"
Case "C": PriorityPoints = "0.13"
Case "D": PriorityPoints = "0.02"

End Select

End Function
********************************************************************

I need some help w/ designing a function that will use "Weighted Averaging".

For describing what I need to achieve in the module, please allow me to
describe it with data that I have calculated in Excel (below &&&&s).

&&&&&&&&&&&&&&&&&&&
1 0.067
2 0.133
3 0.200
4 0.267
5 0.333
-----------
15 1.000
&&&&&&&&&&&&&&&&&&&

The result of A6 = Sum of A1:A5 (or 15)
The results of B1 to B5 are: B1 = A1/A6 (or 0.067); B2 = A2/A6 (or 0.133);
B3 = A3/A6 (or 0.200), etc.
The result of B6 = Sum of B1:B6 (or 1.000)



Now, here's what I'm trying to achieve in the Access module:
- Although the weights (e.g. 0.067, 0.133, 0.200, 0.267, 0.333) are
calculated by "Rank / OverAllSum", I need to assign a different order.

In essence, I want the data to look like this (calculated result is in
reverse order):

1 0.333
2 0.267
3 0.200
4 0.133
5 0.067
-----------
15 1.000

Basically, the number "1 to 5" are priorities that must get a value in
column B. And here, "1" has a greater weight than "5", so I must give "1"
the highest value and "5" the lowest value.


Lastly, the calculation must be dynamic. If I were to add a "6th" record
(priority) the numbers must be recalculatd within making a great deal of
manual changes.

So, with a 6th value, the results (in a query) should look like this:


1 0.286
2 0.238
3 0.190
4 0.143
5 0.095
6 0.048
21 1.000



Thanks in advance,
Tom