Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Historical Price Data (Access XP)

    <font color=448800><font face="Comic Sans MS">I'm beginning a new project for a client in which I will have to store historical price information. I have never done anything similar, and I want to avoid any potential problems down the road by setting it up correctly in the first place. I'm thinking that I will have a detail table with ItemID, SupplierID, Price, and Date. When calculating the cost of a job, the program would pull lowest price for that item as of the date the job was completed (he wants to use lowest price even if he actually used a different vendor). Does this sound right? Any pointers? Any sample databases or reference material that you could recommend?

    And if you have any suggestions on how to set up the query to pull the correct date range, I'd appreciate seeing that, too. I haven't actually sat down to try to figure it out, but right now it seems perplexing. (I should probably add that I've been out of the database business for a couple years while I took time off to adopt two little girls from China. My skills are sadly rusty.) Many thanks!

    --Karyl </font face=comic>
    Attached Images Attached Images

  2. #2
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Historical Price Data (Access XP) - Addendum

    <font color=448800><font face="Comic Sans MS">I guess I should add that there are reasons I'm storing historical price information instead of just job data. This is how I explained it to the client:</font color=448800>

    <font color=black>For example, at some point, we could do an analysis of how much the costs of materials have increased (i.e., TriEd increased their prices 20% in 2002). You could look at salary data in a variety of ways (i.e., Fred has had a 10% increase in wages since he started, etc.). We could also recreate a Job Summary at any time (if errors were made or the record was inadvertently deleted). It would also allow for more accurate results if there were a backlog of jobs to enter. Recent price changes wouldn't affect the results if the program were pulling price and wage data based on the date the job was completed rather than the lowest price or current wage at the time the job was entered into the computer. The current system has this flaw. Jobs completed in 2000 are using price and wage data from 2002.

    Using historical price data, you'd just need to enter the date the job was completed, and the program would search for the lowest price for each item as of that date. Jobs spanning a longer period of time create their own problems in that supplies purchased early in the project might cost less than those purchased later, and wage changes could also impact the results. But I'm assuming that the majority of jobs are completed in a relatively short period of time and any price adjustments would not be that significant in providing you with the data you need to do a cost analysis.

    Another advantage would be the ability to recreate all the Job Summaries for a specific period of time if, at a later date, a significant pricing error was found (i.e., something that should have cost ten cents per item was mistakenly calculated at $10 per item).

    <font color=448800>--Karyl</font face=comic>

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Historical Price Data (Access XP) - Addendum

    This might not be a very useful comment in the big picture, but it sounds as though any system that tracks inventory and cares about the cost of goods sold must contain a solution to this problem. Such systems must track inventory in batches so the use-up assumption

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Historical Price Data (Access XP)

    If you're planning to store historical data for analysis and comparison, you might want to think about storing it in a separate database set up in a star schema as a data warehouse. In that kind of design, your central table has an atomic record that is usually in first normal form but may even be a flat record. The same record includes historical data, including things like calculated code, as well *all* the keys necessary to link it to lookup tables and to join it to other, similar stars. In your case, the basic information would be the detail data but would include not only the date, but the calculated cost, the lowest price, etc. That makes it very fast and easy to pull the information for comparison later on. This is not a database where you enter transactions. It would be updated periodically by appending historical information from your regular database, including the calculated values you need to store.

    I highly recommend a book called "The Data Warehouse Toolkit", by Ralph Kimball, for learning more about Data Warehouse concepts in a desktop database environment.
    Charlotte

Posting Permissions

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