Results 1 to 2 of 2
  1. #1
    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. #2
    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
  •