We install lawn sprinkler systems. We buy all of our inventory at the beginning of the year, and use it up (via installations) through the season. Let's suppose we purchase a sprinkler head for $50 in 2005 from a supplier. Now, lets say that we don't use that sprinkler head for two years because business is slow. There are two pieces of information we need to know, based on the 2 year old price of the sprinkler head:
1) How much should we charge the customer for that head now in 2008?
2) How much should we budget for purchasing more sprinkler heads for 2009?
You might think "go look it up". Well this is difficult when we have over 600 different parts that we need to charge people for and that we need to budget for repurchase.
Does that make any more sense?
MS Access structure as it is now:
Product Table:
ProductID, Supplier, etc.
Orders Table:
OrderID, DateOfPurchase
Orders Details Table:
OrderID (lookup in Orders table), ProductID (lookup in Product table), Quantity, PricePaid
We derive a product's price by how much we are charged at the time of ordering. We don't look up the price in a catalog or something. Now, we may order the same part a few times during the year because we've run out. Therefore, we use the lowest price paid during the year for that product's price.
*Deep breath*
So, with a query I get a list of the minimum price paid for each product for which we've ordered and use that number for billing and/or budgeting purposes.
Now, using the example at the beginning, how would I adjust the $50 price tag on the sprinkler head to what it might be now? We have a good idea of the percent increase in products from year to year and we need to use that percentage to bump the price on these items.
Does that make any sense?