Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Self-Abusing Formula (A2K)

    At its
    Cheers,
    Andy

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

    Re: Self-Abusing Formula (A2K)

    Andy, I'm not exactly sure what you're trying to get your formula to do (As far as the order it is supposed to be calculated), but if you're just getting the wrong number, don't forget that multiplication/division take precedence over Addition/subtraction... <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    To store your "previous average cost", you could have an update query before you run your above calculation, making a separate column in your database "previous average cost" = "current average cost", and then update your current average cost.....
    ____________________________
    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

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

    Re: Self-Abusing Formula (A2K)

    Jeremy,

    Let me mull over your comments so that I can get back to you with:

    a) A more semi-intelligent description of what I'm after, and

    [img]/forums/images/smilies/cool.gif[/img] Hopefully an example database of what I'm attempting.

    Again, as I've mentioned before, this is one of those stupid things that are easily done with pencil and paper, but .....
    Cheers,
    Andy

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Self-Abusing Formula (A2K)

    Costing inventory can be very complex, and it helps if you can relate exactly what you are trying to do. That is, are you trying to determine the average cost of parts that you have bought? Or are you trying to determine the average cost of the parts currently in inventory? The formula you have is useful for calculating the average cost of the current inventory. The new average cost would be calculated each time you received inventory. You could have a table which basically has Part#, AveragePrice, and Date. This would give you a nice history. You write this new record each time after calculated the new Average (getting the Old Average from the most recent record on file).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Self-Abusing Formula (A2K)

    Mark,

    I thought it was just me being thick. Yes, it is very complex when programmed, but seemingly simple manually. As I mentioned to Jeremy, I'm going to try to put this into words first and then, worst case scenario, try and database it. I feel like I'm flying around in ever decreasing, concentric circles and I'm afraid that .....
    Cheers,
    Andy

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

    Re: Self-Abusing Formula (A2K)

    Mark,

    I've attached a document that I hope may clarify my clouded description. I swear Lamont Cranston is lurking about here somewhere,

    and thanks in advance for having a look at this,
    Cheers,
    Andy

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

    Re: Self-Abusing Formula (A2K)

    Mark,

    My apologies in being so slow in getting back to you with a sample of my pseudo-inventory system. If you are feeling at all masochistic with respect to your own time, you might have a look at this.

    Basically, when you open up the database it will take you to the Inventory Master screen and if you then click on the printer icon next to the "Summary" sign, it will then take you to the empty report that has a depiction at the top of the order of entry of transactions and the wished for Average Cost results at each stage.

    If there is anything else that I can offer to make this a little more clear, please don't hesitate to let me know.

    The long diatribe that I included in my last Word document to you pretty well spells out the order of entry to achieve the samples needed.

    I really appreciate any time you might spend on this. Also, if you don't have the time, also let me know.

    Thanks again to all that have contributed to help me get this far,
    Cheers,
    Andy

Posting Permissions

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