Results 1 to 3 of 3
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    "Snapshots" or History?

    I'm getting into one of those areas that I barley understand, I know this is a big concept so I am hoping someone can point me to an example in NorthWind that might help, or tell me what this concept is so I can search and read up on it.



    I am trying to Capture a set of data at a moment in time, I guess similar to a receipt for a transaction, where the price of an item on the receipt is permanent even thought the price of an item may change.

    what i need to do is track deadlines and fines.


    for example in May 2020

    Who Item A Item B Fine
    Person 1 No No $200
    Person 2 Yes No $100
    Person 3 Yes Yes $0


    now this is great except at some point person one is going to turn in Item A and Item B and those columns will say yes and yes.
    But I am going to want to look back and see in May they had not turned in their items

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would be storing that fine amount with the record. So Person1 would have two records, one for A and one for B.
    Then I would be storing what payments have been made (where is up to you, possible in another field in the record even?) and then calculate balance. NEVER store the balance.

    HTH

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am not familiar with your application. However, my understanding of your post is that there are some "events/milestones" that occur within your business. Some info about each of these events/milestones should be recorded (similar to audit/transaction log). Further, some events may change/alter the result of an earlier event.

    Consider, the Price of Item X. On May 1 you get a new supply of Item X; the sale price is $10.00 per. Client 1 purchases 3 of Item X for a total of $30 on May 5. Client 2, who has purchased several things from you and is a loyal client over several years, buys 20 of Item X on May 12. Now, because he is a loyal customer you decide to sell these Item X to him for $8.00 per for a total of $160. Also, on May 15 you have been informed that the any new Item X you purchase from your supplier will now cost you 50% more as of June 1. You now(May 16) adjust your selling price of Item X to $14 per.

    All of this to show the Price of an Item changes with time. There is the Price of an Item at some point in time. There may be clearance sales, loyalty programs, other specials etc that could affect the price at which you sell the Item to a customer. The key is to recognize that these prices do and will change. The approach is to record the quantity and selling price of an Item with the OrderDetail record. This records the price of this item to this customer on this date, and allows for the various sales, loyalty programs, discounts etc without affecting the current or next or previous "list/standard price" of the item. It provides for a historical record of the sales of the item also. You do not rely on the current list price that is stored in the product table for historic sales.

    I often use the AgreedUponPrice and store it in the OrderDetail record. Some will suggest to have a PriceEffectiveDate in the Product table --but it can get complicated for Sales, loyalty etc.

    I recommend you look at the various events during your process. Then, have a transaction table to identify and record the date and time and details of any event. The date/time stamp allows you to see things in a date order, and the details provide related info.

    Good luck.

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

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  3. Replies: 6
    Last Post: 12-22-2015, 09:49 PM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 1
    Last Post: 09-03-2014, 03:27 AM

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