# Thread: Calculating weighted averages in queries

1. Registered User
Join Date
Mar 2017
Posts
2

## Unanswered: Calculating weighted averages in queries

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!
Last edited by bibicul; 03-15-17 at 06:19. Reason: error

2. Registered User
Join Date
Mar 2017
Posts
2

## i try with...

Public Function SumProduct(Array1 As Variant, Array2 As Variant) As Variant

Dim lngRowMax As Long
Dim lngColMax As Long
Dim i As Long
Dim j As Long
Dim var As Variant

' Compute the littlest upper dimensions (MaxRow, MaxCol) of both arrays
'
lngRowMax = IIf(UBound(Array1, 2) <= UBound(Array2, 2), UBound(Array1, 2), UBound(Array2, 2))
lngColMax = IIf(UBound(Array1) <= UBound(Array2), UBound(Array1), UBound(Array2))
ReDim var(0 To lngRowMax)
For i = 0 To lngRowMax
For j = 0 To lngColMax
var(i) = var(i) + Array1(j, i) * Array2(j, i)
Next j
SumProduct = SumProduct + var(i)
Next i

End Function

Any help?
Last edited by bibicul; 03-15-17 at 06:40. Reason: suggest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•