Results 1 to 2 of 2
  1. #1
    Stressed is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010
    Posts
    19

    Managing Point in Time

    Good morning all,

    I have been trying to figure this out all weekend.

    Let me start off by saying that I by no means a programmer. I can fumble my way through most VBA using the web or bouncing ideas off others.



    I have a situation now where I need to be able to historically change data without physically updating the existing records "important".

    Right now, I have three cases

    1. the new effective date is = to the existing effective date - ACTION: update the existing record with a replace timestamp; insert a new record with the new information with the same effective date

    2. the new effective date is > the existing effective date - ACTION: insert a copy of the existing row with an end date one day less then the new effective date; update the existing row with a replace timestamp; insert a new row with the new information (effective date one day > than the end date.

    3. This is where my issue is... the new effective date < the existing effective date. When there is only one record, this isn't an issue. I can just follow case 1 but when there is more then one record then there is a problem. I can not have 2 or more records "effective" in the same time period.

    I need to find a solution that will enable me to search for existing records where the new record will be in between and then update each row in succession accordingly.

    I hope that I have written out my issue properly.

    Any help, pointers, links to other solutions would make my day!

    Thanks for reading!

    Stressed

  2. #2
    Stressed is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010
    Posts
    19

    Solved

    I figured it out.

    Steps:

    1. Find record where the new effective date is between the old effective date and end date

    2. Copy that row.

    3. Logically delete that row

    4. Change the end date to one day less than the new effective date

    5. Logically delete all records where the new effective date is > the current effective date.

    6. Insert the new record with the new effective date.

    Complicated process but I end up with all the history that the user makes in real time.

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

Similar Threads

  1. Time Query
    By kbsudhir in forum Queries
    Replies: 1
    Last Post: 07-06-2009, 05:19 AM
  2. Displaying Time Only
    By Corinne in forum Access
    Replies: 5
    Last Post: 06-24-2009, 09:45 PM
  3. Time Comparison
    By Larry819 in forum Queries
    Replies: 1
    Last Post: 06-22-2009, 09:26 PM
  4. Time
    By bertenbert in forum Queries
    Replies: 1
    Last Post: 08-04-2008, 03:05 AM
  5. Point of Sales Application
    By chico_yallin in forum Access
    Replies: 1
    Last Post: 06-23-2007, 05:30 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