Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Posts
    12

    Unanswered: How Calculating Weighted Average In Query

    hi, every body..
    how can i calculate the weighted average in query?

    Example :
    -i have table for income "Purchases"contain: details for Item "Mouse Optical"
    _________________________________________
    item_code & Qty & Price & Date -------& Value
    A1-----------50---- 95----1/11/2008----4750
    A1-----------7----- 85----2/11/2008----595
    A1-----------5----- 50----3/11/2008----250
    _________________________________________
    in this case my cost for this item= total Value / Total Qty
    cost = 90.24

    - and another table for outcome "Sales" contain :
    item_code & Qty & Price & Date
    A1-----------5---- 100----4/11/2008
    in this case my Store Balance from item "Mouse Optical"= Income Qty - Outcome Qty
    Store Balance Qty= 57
    my store Value= Store Qty* cost
    etc...

    is there away to calculate a weighted average by query????
    thanks for helping me.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT item_code, SUM(Price*Qty)/SUM(Qty) as weighted_average
    FROM my_table
    GROUP BY item_code
    Sorry - I do not understand the second part for sales. You will need to explain that again. Where does store balance come from? Where are your income Qty and outcome qty values? Please explain the second example as clearly as the first.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2008
    Posts
    12
    Thanks pootle flump,
    Code:
    SELECT item_code, SUM(Price*Qty)/SUM(Qty) as weighted_average
    FROM my_table
    GROUP BY item_code
    this will calculate all Purchases Prices,, Result.. Not true
    Where does store balance come from?
    from "Purchases Qty - Sales Qty"
    Otherwise..
    Income - outcome
    Where are your income Qty and outcome qty values
    value = Qty * Price ' as showed in example

    the idea is how to calculate the Store Balance Value???? Only Last Qty + New Qty,, Last Cost Price + New Cost Price..

    In Excel ..
    Item Cost = (Store Balance Qty * Cost Price ) + (New Purchase Qty * New Price) / (Store Balance Qty+New Purchase Qty )

    But How Claculate This In Access?

    There Is Idia from Friend:
    Code:
    Price:(DSum("[Price]*[qty]";"Purchase";"[sn]<" & [sn] & " And [Code_Sub_Item]='" & [Code_Sub_Item] & "'")
    -nz(DSum("[Price]*[qty]";"Qry_Cost";"[sn]<" & [sn] & " And [Code_Sub_Item]='" & [Code_Sub_Item] & "'");0))
    
    /(DSum("[qty]";"Purchase";"[sn]<" & [sn] & " And [Code_Sub_Item]='" & [Code_Sub_Item] & "'")-nz(DSum("[qty]";"Qry_Cost";"[sn]<" & [sn] & " And [Code_Sub_Item]='" & [Code_Sub_Item] & "'");0))
    Where [SN] Placed in Table Of Purchase And Table Of Sales to Define The transactions from Income & Outcome

    But when This query run !! It's Calculate Very Slowely And then access Hangup..

    I wish I Could explain Clearly..

    Thanks Again
    Your's
    Hany Awad

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - one thing at a time
    Quote Originally Posted by hongylover
    this will calculate all Purchases Prices,, Result.. Not true
    Are you saying it is wrong? It returns the value you wanted from the data you supplied - correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2008
    Posts
    12
    I will explain the problem in ur Query..
    if we have 5 Qty from Item With cost Price = 90.24 in Store this day .. Ok
    tomorrow we sell all the Qty.. ok
    store Balance now = 0

    we Buy New Qty =10 With New Price = 50
    so Last Cost For this Item = 50

    Try It in your Query..

  6. #6
    Join Date
    Nov 2008
    Posts
    12
    up for advice

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't even understand the problem.

    It really doesn't help when you don't answer mods' questions.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thank you - I must confess I did give up.

    OP - your very first illustration, with the data and expected output, was excellent. Please can you repeat this with your new requirements (new data, required output). I am sure this is possible, you just need to phrase the problem.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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