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

    Unanswered: Average Purchase Price - Fed Up

    Hi,

    I have a db created for a spare parts shop. The main thing disturbing me is the Average Purchase Price. The Db contains lot of forms and tables but I would like to mention here only those that concern with avg pur price.

    Tables
    1. ProductMaster
    2. T_PurInvFooter
    3. T_SalesInvFooter

    Forms
    1. F_Product_Master
    2. F_PurInvHeader
    3. F_PurInvFooter
    4. F_SalesInvHeader
    5. F_SalesInvFooter

    Header and Footer forms are related on one-to-many basis.

    F_PurInvHeader contains following fields
    a. InvNum (Autonum One-to-Many relationship)
    b. Date
    c. SuppCode
    d. SuppName
    e. SuppInvNum

    F_PurInvFooter contains following fields.
    a. ItemCode
    b. ItemName
    c. Stock (This is calculated field and producing correct result)
    d. Qty
    e. Price
    f. AvgPurPrice
    g. Amount
    h. InvNum

    Now what I did and what my client need is as follows

    Client want to appear Average Purchase Price once he started entering purchase entries. Example: Item A purchased 3 times with different prices

    (Table is available in the attached documents to view how Avg Pur Price is calculated)


    This is ok. I somehow handled it to display the Avg Pur Price. Then main problem occurred when the stock became ZERO and then again, we purchased same items. This time it was considering all the previous purchase quantities and calculating avg pur price.

    Let us say the last avg pur price was 1.26 but once the stock becomes zero and purchase same item next time, the average purchase price should be the one that we purchased

    Example.

    If we purchased item A , 3 pcs at the rate of Dollar 1.75. So the Average purchase price will be 1.75 as long as we dont make another purchase entry.
    But my code considering from beginning qtysay 16+12+4 and finally + 3 which becomes 35 and it is summing all the amount and dividing to get avg pur price which is totally wrong.

    Considering above table, the last avg pur price comes 1.28 which is ok but once the stock is ZERO it should sum the purchase qty and price only from when the stock became zero while it wrongly calculating as follows.

    35=(Qty 16+12+4+3)
    46.49=(16x1.25=20)+(12x1.30=15.60)+(4x1.41=5.64)+( 3x1.75=5.25)
    : 46.49/35
    :1.33

    The new avg pur price is coming as 1.33 which is totally wrong. It should be 1.75 for the 3 pcs we purchased after ZERO stock.

    I am fed up of this situation. I would be gratefull if some can help me in this regards. If needed I can upload here my db with least records and objects. I am sure it will be more clear after seeing my real db.

    With kind regards,
    Ashfaque
    Attached Files Attached Files
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Usually I would say that the reason for your problem is that the data is not normalized. It is true that your data is not normalized - there are many good articles written on normalization of data, and I suggest that you read up on the subject and modify your table accordingly - but that's not the main problem, the way I see it.

    The way I understand the situation is that you're attempting to both record purchases and control inventory using the same fields. When stock goes to 0 you have issues with the data.

    I suggest that you keep the table as a purchasing record only, and design a new table for inventory control. All stock additions and withdrawals should be added/subtracted to/from that table only.

    Hope this helps,
    Sam

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Sam,

    I have taken a step towards normalization. I feel that my tables are simple now after some changes I made as below.

    I removed calculations over stock field in F_PurInvFooter form and kept is as numeric field that will receive data from table ProductMaster upon updating of ItemName Or ItemCode in F_PurInvFooter . Because I have created another field by Stock in T_ProductMaster which stores the calculated Stock each time when there is purchasing.

    In other words, once we purchase some quantity of a particular item, it would update calculate its current available stock and stores in T_ProductMaster. This process is being carried out thru few code lines on after update event of Qty field in F_PurInvFooter.

    So now the stock showing after update of ItemName Or ItemCode is coming directly from tbl T_ProductMaster.

    But my main concern is avg pur price. How can it be calculated when Stock goes Zero.

    I think that there should be a date field in tbl T_ProductMaster that would record the last purchase date that comes from last purchasing of concern item in F_PurInvFooter. So when user updates ItemName Or ItemCode in F_PurInvFooter , it should check in tbl T_ProductMaster for stock and its last purchase date. If stock is previously stored as ZERO and there is last purchase date, it should only sum all purchased qty after that date from F_PurInvFooter. Else otherwize should count all the quantities since begining.

    But I am not sure this would work out.

    Any other idea gents?
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I'm afraid you might have misunderstood my intent. In order to normalize the data so you can do what you want, you need to have two separate, independent tables, one for inventory control and one for purchasing control. They do, of course, have common keys, but that's about the only thing they have in common.

    The purchasing table would have the following fields (at a minimum. You will need more fields to satisfy other user needs):
    item
    qty
    purchase $ total amount
    average price this item
    average price this item to date

    (I know I'll be taken to task for this, because many users - myself included - believe that 'calculated values have no place in a table.' However, guys, I think it's different here, because I think Ashfaque's user wants to be able to identify the trend of the average purchase price as well as the average price itself. In order to have this info, he needs to record it in each purchase record, as it's tedious to re-create it.)

    The inventory detail table would look like this (at a minimum, as above):
    item
    qty
    added/subtracted (need to know if it's an addition to stock or a withdrawal)
    xaction date

    An additional inventory master table would have this structure (at a minimum, as above):
    item
    description
    current stock (calculated on the fly each time a transaction takes place in the detail table).

    Thus the record you presented in your original post, which allows three transactions to be recorded - which prompted me to say the data isn't normalized - would shrink to only one transaction per record, and be normalized to the third level. All average data would be calculated programatically, either in VBA or in invisible cotrols on the form.

    Additionally, if I were king, I'd do it this way. When the item is received, only the purchase table is updated. The inventory table would only be updated when a stock clerk enters the item into the stock room and records the transaction in the program.

    I apologize if my original intent wasn't clear. Good luck,

    Sam

Posting Permissions

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