Calculating weighted averages in queries

[bibicul]

Hi everyone,
I'm working on a database that needs to value FIFO
inventory based on actual costs per unit. The source data
is in a query that combines item receiving/return history,
and sorts the results by item ID and by receipt date.

So, what I get from this query looks like this:

ITEM # RECPT_DATE QTY UNIT_COST
A1 10/15/2001 100 5.50
A1 09/02/2001 50 6.00
A1 06/10/2001 40 7.00
A2 12/02/2001 75 15.00
A2 07/20/2001 20 14.50

Now, I have another table that tells me the total on hand
quantities of each item in inventory.

ITEM # TOT_QTY
A1 [WEIGHTED average cost]
A2 [WEIGHTED average cost]
Since I only want to get the WEIGHTED average cost of
these items, I need the total quantity by item table to
limit the calculation on the first table to a subset of
the most recent recipts that reach the total on hand
quantity of that item.

So, is this possible to do with VBA?

Any ideas? ANY help would be much appreciated!!

Thx.

[JeffChr]

Your question is difficult to understand. Do you want to use LIFO, FIFO or Weighted Average. You can only use one. Your first paragraph says FIFO. Your final paragraph says Weighted Average AND you want to limit the calculations to the most recent receipts which if LIFO. So the first thing you need to do is define your problem The solution is simple. ;-)

[bibicul]

Hi,
I want to use Weighted Average, meth.
Thx.

[JeffChr]

What are the names of your tables?

[bibicul]

Hi,
tblData and qryWA
I try with Funct.



Public Function WA(Fieldx, Fieldy, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Double
Dim vFld1 As Double

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = 0
vFld1 = 0

SQL = "SELECT [" & Fieldx & "] as Fld,[" & Fieldy & "] as Fld1" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value




rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = (vFld + rs!Fld * rs!Fld1)
vFld1 = vFld1 + rs!Fld1

rs.MoveNext
Loop

vFld = vFld
vFld1 = vFld1
Set cnn = Nothing
Set rs = Nothing


WA = vFld


End Function

[JeffChr]

interesting. i see values accumulating but not a weighted average computation. i will work something up for you to look at.

[JeffChr]

one more question. Your qryWA has these two fields: ITEM # and TOT_QTY. Is TOT_QTY a quantity field or a weighted average field? The template data shows [WEIGHTED average cost] but the field name is TOT_QTY.

[bibicul]

Hi,
Weighted average computation is in an other field in query, but i want to make tree meth. to calculate value stock, LIFO,FIFO and WA