Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Top Urgent: Average Price Calculation - A bit critical

    I am trying to update average price for the item in a trading business.

    The story is like this.

    I have 4 tables named. The main concern fields are mentioned in front of the tables.

    1. Product_Master (ProductCode, OpeningBal, OldPurPrice, AvgPurPrice)
    2. T_PurInvFoot (ProductCode, CurrPurPrice, PurQty)
    3. T_SalesInvFoot (ProductCode, SalesQty, SalesRetQty, SalesRetAmt, AvgPurPrice)
    4. T_MInv_Foot (ProductCode, AvgPurPrice, Qty)

    My client need average price in AvgPurPrice column which should update automatically in Product_Master table whenever there are transactions of new purchasing or sales returns. I tried with different ways but won’t work. Here is the detail what we need.

    Let us assume that ProductCode 1 is available at present in Product_Master table along with its other detail as below.

    All values in Product_Master table will be steady for a product (item) except AvgPurPrice because AvgPurPrice price will be changed at every transaction. But at the beginning (at first entry in product master) AvgPurPrice value will be same as purchase value. (I mean 20). I found variance in Average price at the 5th transaction which is in fact 2nd entry of the purchasing. When I purchased 11 items at the rate of 33

    The average price should be calculate on EXISTING STOCK QTY that shows at the time of transactions.

    So here it goes.

    OB= Opening Balance
    P= Purchasing
    S=Sales
    SR=Sales Return
    PR= Purchase Retrun.

    (Please refer to my Excel table I attached herewith)

    New purchasing price could be different from the previous one for the same item hence the average purchase price should update at every transaction. This will help to calculate the value of the current stock.

    Please note that I Sales retrun qty and its amount field I keep in in the same table called T_SalesInvFoot to avoid dealing with multi tables. The same way, Purhase return qty and its amount filed is placed in same table called T_PurInvFoot.

    There will be no return qty in maintenance table called T_MInv_Foot.

    To do this all what I put the VBA code on after update event of PurQty field in subform (based on T_PurInvFoot) is as follows.

    All Blue color code lines are fine. Just Red ones are not properly giving desired result.


    Dim OBPurTrans As Long
    Dim OBPrice As Double

    Dim CurrStock As Long

    Dim OBValue As Double
    Dim PurQtyValue As Double
    Dim SalesRetValue As Double
    Dim SalesValue As Double
    Dim PurRetValue As Double
    Dim MaintQtyValue As Double
    Dim TotValue As Double

    Dim AvgPrice As Double

    Me.ProductCode.SetFocus

    'Count Total Pur transactions till date + 1 transaction of Opening balance


    OBPurTrans = DCount("*", "T_PurInvFoot", "ProductCode=" & Me.ProductCode.Text) + 1

    OBPrice = Nz(DLookup("OldPurPrice", "Product_master", "ProductCode=" & Me.ProductCode.Text), 0)
    OBValue = OBPrice * Nz(DSum("Openingbal", "Product_master", "ProductCode = " & Me.ProductCode.Text), 0)
    PurQtyValue = Nz(DSum("Amount", "T_PurInvFoot", "Productcode = " & Me.ProductCode.Text), 0)

    SalesRetValue = Nz(DSum("OrigRetAmt", "T_SalesInvFoot", "Productcode = " & Me.ProductCode.Text), 0)
    SalesValue = Nz(DSum("Amount", "T_SalesInvFoot", "Productcode = " & Me.ProductCode.Text), 0)
    PurRetValue = Nz(DSum("PurRetAmt", "T_PurInvFoot", "Productcode = " & Me.ProductCode.Text), 0)
    MaintQtyValue = Nz(DSum("Amount", "T_MInv_Foot", "Productcode = " & ProductCode), 0)

    TotValue = (OBValue + PurQtyValue + SalesRetValue) - (MaintQtyValue + PurRetValue)


    CurrStock = Nz(DSum("Openingbal", "Product_master", "ProductCode = " & ProductCode), 0)
    CurrStock = CurrStock + Nz(DSum("PurQty", "T_PurInvFoot", "Productcode = " & ProductCode), 0)
    CurrStock = CurrStock + Nz(DSum("SalesRetQty", "T_SalesInvFoot", "Productcode = " & ProductCode), 0)
    CurrStock = CurrStock - Nz(DSum("SalesQty", "T_SalesInvFoot", "Productcode = " & ProductCode), 0)
    CurrStock = CurrStock - Nz(DSum("PurRetQty", "T_PurInvFoot", "Productcode = " & ProductCode), 0)
    CurrStock = CurrStock - Nz(DSum("Qty", "T_MInv_Foot", "Productcode = " & ProductCode), 0)


    AvgPrice = TotValue / CurrStock


    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("select * from Product_master where Productcode=1")
    If rst.RecordCount = 1 Then
    rst.Edit
    rst!AvgPurPrice = AvgPrice
    MsgBox "New Avg. Pur. Price will be " & AvgPrice & "", vbInformation, "AlRasasia Inf"
    rst.Update
    End If
    rst.Close
    Set rst = Nothing


    There is no error in the code. I receiving the results. But once the Sales retrun transaction appears, the average price appears either more or less ( with big variance). Hopefully I explained clearly from my site.

    I am fedup of this calculation. I would appreciate it very much if someone could correct my code logically.

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Oh, I forgot to attached the sheet..

    here it is..
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Y I did it...

Posting Permissions

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