Results 1 to 6 of 6
  1. #1
    mubtuhogar is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    1

    Handling Inventory Costs That Change Over Time

    Hi All,

    On an inventory database, product costs change over the time. How do I maintain proper costs/prices on all invoices that do not get recalculated when new costs are setup for that same product.

    Example,

    On February 2010 we sold a TV Set for $300.00. Our cost for that TV Set on February was $200.00. How ever, today, the same TV Set now costs $225.00. When I add this new cost to the database it modifies all my previous transactions for that same TV Set and updates the cost to $225.00. How can I keep the previous transactions as they were entered with that previous cost of $200.00 and allow all future transactions to use the new cost of $225.00?

    I am using Access 2007.

    Thanks!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Save the cost with your invoice.

  3. #3
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,171
    or have a table with historical prices, with the "priceValidFrom" and "priceValidTill" fields

  4. #4
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    What cost flow assumption are you using? FIFO, LIFO, weighted average, or perpetual average?

    Similar to RuralGuy's suggestion, I usually place some code in the After Update event of either the inventory code or inventory quantity to fetch the appropriate price of an item given some date criterion.

    As for Noella's suggestion, I would create an inequality join query.

    In most of my projects, I have made a combination of these two approaches work to my satisfaction.

    Good luck.

  5. #5
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,171
    Hi Evander,

    most used in warehouses (in Belgium warehouses anyway) is FIFO. Sometimes extra costs are added to the product (storage costs depending on the time, repairs ect... ). What I learned, if you deal with external customers, most of them have their own discussed price settings, and the commercial boys can make it pretty complicated. If it's an internal service very streight forward tarifs table is used.

    grNG

  6. #6
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    I think RG's advice hits the issue most directly - though somewhat brief. Generically there are alot of cost elements that change in time - one example is the sales tax rate. One needs the 'transaction' table (in RG's advice the invoice) that records all the correct data of that transaction event.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error Handling
    By trb5016 in forum Access
    Replies: 2
    Last Post: 08-10-2010, 08:37 AM
  2. Run-time 339 when change to 2007
    By nivek in forum Access
    Replies: 1
    Last Post: 06-30-2010, 08:40 PM
  3. Report w/o multiple ship costs
    By stattech in forum Reports
    Replies: 1
    Last Post: 06-22-2010, 04:20 AM
  4. #error handling
    By mws5872 in forum Access
    Replies: 4
    Last Post: 05-12-2010, 07:06 AM
  5. Subform Change - Run-time error 2101
    By Cheshire101 in forum Forms
    Replies: 3
    Last Post: 12-21-2009, 12:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums