Page 1 of 4 123 ... LastLast
Results 1 to 15 of 53
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inventory Calculation (A2K)

    It
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inventory Calculation (A2K)

    I'm confused indeed. You state "Cost per item on 1st order is $5 & the quantity received is 10.", then in the calculation ( QtyRcvd * Cost ) seems to correspond to ( 12 * $7.00 ). <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    Hans, I've been glancing at this...I think 12*$7.00 is the new shipment.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    Hans,

    And confused you should be based on my explanation. It always looks OK until, even after previewing it, I post it. Something loses in the translation between my mind and my keyboard.

    So, that being said, try, try again.

    1. First order ever to go on the system:

    Qty: 10
    Cost per: $5.
    Therefore, QOH = 10
    AverageCost: $5.

    2. 6 removed from stock
    Therefore QOH = 4

    3. Second Order ( and same logic to be applied to all future orders )

    QtyRcvd: 12
    Cost: $7 per

    This is the formula that they want to see for all future orders:

    (((( QtyRcvd * Cost ) + ( AverageCost * QOH )) / (RcvdQty + QOH)

    (( 12 * $7.00 ) + ( $ 5.00 * 4 ) / (12 + 4 )

    $84.00 + $20.00 / 16 = $ 6.50 = New Average Cost

    How do I bring in the New Average Cost into my next calculation where it would be come the Current Average Cost?

    Hope this helps. Thanks for looking at this for me,
    Cheers,
    Andy

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    When you store the value "quantity", is it stored in a field, or is each item entered "quantity" number of times, linked to a PK, or something along those lines?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    Jeremy,

    I appreciate your looking in. The setup is as follows:

    There is a Master table of Parts with descriptions & stuff.

    There is a linked Receiving table that stores the quantity and cost for each order coming in.

    It's the manipulation of the first Average Cost vs the future calculation of Average Cost that has me confuzzed.

    Hope that helps,
    Cheers,
    Andy

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    From your explanation I think the average cost should be stored in the master table. In your related table, the average price for each order is the original price, so no calculation is needed there. I'm working on some sort of totals query or something similar, with a temporary field for average price, grouping by partid. Hopefully I'll be able to set it up to use the end result in an update query to update the average cost in the master table... perhaps my telling you what I'm attempting will give you new ideas (if you haven't thought of this already)
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    Jeremy,

    Good Thoughts!! I've bandied about some various scenarios, most of which came back and bit me, but I'm going to have a go on variations on your themes. Will let you know how it goes.

    Thanks again,
    Cheers,
    Andy

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    As soon as I figure out how to update your table from a totals query...I'll post a very simple example <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    Thanks muchly, looking forward with eager,
    Cheers,
    Andy

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    <P ID="edit" class=small>(Edited by NYIntensity on 29-Jul-05 01:22. Note to Hans: I know you detest storing calculated values...any suggestions?)</P>Alright, here's how it works...
    I have two tables, tblParts, and tblOrders. tblParts contains your parts (obviously), the part ID, the quantity on hand, and the average price. tblOrders contains order ID, part ID, quantity recieved, and the price received at.

    qryPartTotals is a simple totals query which groups tblOrders by Part ID, summing the quantity on hand and averaging the price. qryUpdatetblParts is an SQL query which updates tblParts using DLookup statements. I hope this is what you were looking to achieve.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
    Thanks Hans.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inventory Calculation (A2K)

    Note to Jeremy: Inventory databases are an exception to the rule that you shouldn't store calculated values: if you want to keep everything dynamic, the calculations can become very complicated.

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    Jeremy,

    Thanks kindly for your extra work. I guess the only question I was going to raise, and Hans has already answered it, and that is the storing back of a calculation in a table. I'm going to have a go at this first thing in the morning when I can see straight ( OK, not as crookedly ) and get back to you.

    Again, it's appreciated,
    Cheers,
    Andy

  15. #15
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Calculation (A2K)

    Andy,
    Could you check out the formulas and tell me if they give you the result you wanted? For example,
    an order for 1 piece of part 1 at $4 and an order for 3 pieces of part 1 at $8 gives the average cost of $6.....is that what you reqest?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Page 1 of 4 123 ... LastLast

Posting Permissions

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