Consulting

Results 1 to 8 of 8

Thread: Calculating weighted averages in queries

  1. #1
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    4
    Location

    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!!

    Thx.

  2. #2
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    4
    Location
    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. ;-)

  3. #3
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    4
    Location
    Hi,
    I want to use Weighted Average, meth.
    Thx.

  4. #4
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    4
    Location
    What are the names of your tables?

  5. #5
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    4
    Location
    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

  6. #6
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    4
    Location
    interesting. i see values accumulating but not a weighted average computation. i will work something up for you to look at.

  7. #7
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    4
    Location
    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.

  8. #8
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    4
    Location
    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

Posting Permissions

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