Results 1 to 3 of 3
  1. #1
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24

    Tables & Relationships: Maintaining Historical Accuracy for Records That Are Subject to Change.

    I am building my first Database and I am struggling with how best to design my tables and relationships and to achieve my goals.



    I am designing a payroll database and I need to know, what is the conventional design/structure for records that are subject to change but still need to retain historical accuracy?

    For example: I have an employee who earns $12.00 and hour and I pay them at that rate for for a whole year, then I give them a raise to $13.00 and hour. If I change the record to reflect their raise than I will ruin the historical accuracy of my database because the $12.00 p/hr rate will been overwritten the new $13.00 p/hr rate. If I add a new pay rate every time an employee gets a raise then I create a messy situation where one employee may end up having several different pay rates linked them over the span of their employment and it may not be clear what their current rate is. I could create a note that displays the employee's current hourly rate, but then I would have to manually type in the current rate every day for every employee instead of having it auto-fill like it currently does.

    I assume there is a simple solution to saving historical accuracy while updating information but I am new to database design and I am struggling to find an efficient way to structure my tables and relationships to accomplish this. Any advice, or resources that will point me in the right direction would be greatly appreciated.

    Edit: The simplest idea I have is to add a yes/no column in my table, tblEmployeePay, that designates if the pay rate is current (yes) or old (no). Then I will have to add in a new pay rate whenever there is a change, mark it as 'yes' and mark the previous pay rate as 'no'. I'll also have to figure out how to make sure my pay rates marked 'no' do not show up on any of my data entry forms but that seems like that should be pretty easy with a IIF condition...is there a better way?
    Last edited by ExtremeNovice; 03-27-2017 at 03:27 PM. Reason: Adding On

  2. #2
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Easiest way and one I've used is include a begindate and enddate Current payrates have a null for the enddate and you're payroll queries just need to include enddate = null

  3. #3
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    Ah yes, that is a much better idea, and it will, by default, also record when the change was made. Thank you!

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

Similar Threads

  1. Replies: 10
    Last Post: 12-07-2015, 02:56 PM
  2. Replies: 1
    Last Post: 10-21-2015, 09:56 AM
  3. Replies: 1
    Last Post: 10-10-2014, 10:06 AM
  4. Enforcing Data Accuracy from FK
    By Minerva in forum Access
    Replies: 3
    Last Post: 02-21-2010, 04:01 PM
  5. Appending Records to tables with relationships
    By RubberStamp in forum Import/Export Data
    Replies: 0
    Last Post: 12-14-2008, 06:52 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