Results 1 to 6 of 6
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Archiving Employee Data + Querying To Find Changes


    Good morning,
    In my team's database, we import a file that contains employee information - specifically, Employee Name, Job Title, Department, and Manager.
    I created a process to archive the data each month, so essentially there's multiple tables:
    tblEmployees_Current_Month
    tblEmployees_November2018
    tblEmployees_October2018
    tblEmployees_September2018
    etc.

    The problem is, I'm now attempting to create some queries to identify employees whose Title and/or Department and/or Manager has changed within a specific time range. One of which would identify changes that have taken place since last month (e.g.: compare tblEmployees_Current_Month to tblEmployees_November2018 to find changes), and another to identify changes that have taken place in the past quarter (compare tblEmployees_Current_Month to tblEmployees_September2018)
    However, the archive tables are named after a specific month/year - so the queries would have to be updated each time (e.g.: tblEmployees_September2018 is 3 months ago as of today, but then next month it would be tblEmployees_October2018).

    So I'm trying to figure out if there's a way to go about this without having to update the queries each month to use the appropriate archive tables. Or, should I scrap this and create a different solution - e.g.: instead of archiving the data into a separate table for each Month+Year, just create a single table and keep appending data (with an "Import Date" field which would indiciate when that data was appended to the table)

    Any help would be appreciated. Thanks!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    At first glance I'd say your problem is that you've compartmentalized what is basically the same data. Seems to me that all that data should be in one table where you have a field to archive it. That could be "Archived" - holding the date that the record was archived. Your approach would require modifications to queries and forms based on changes to data, which is a good indication of improper design.

    So yes to the second part of your post. Use whatever field name/date makes sense to you.
    Last edited by Micron; 12-01-2018 at 12:05 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree 110% with Micron.
    One of the biggest mistakes I ever made was to archive student data at the end of each academic year.
    The other was separating out all leavers data into separate tables... So of course I needed archived leavers tables as well.

    The result was 4 sets of tables, forms, queries and reports instead of one of each which created a huge amount of extra work.

    The third mistake was that when i realised the idiocy of this approach, I said it was too late to reverse those decisions which of course meant the extra work continued
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    fletcjas is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Lincoln, UK
    Posts
    23
    I have to do a similar thing, but mine is due to DPA (GDPR).
    I have devised a way of archiving data and/when needed, generally this would be after 1 year of no data update.

    The solution i have made is to have a table that the table is moved into (this isn't visible on any tables) and then run the queries on the premise of:
    Select * from **MEMEBERS** where Archived = 'no' and **xxxxxxx**
    Dont know it this is any help.

  5. #5
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81
    Quote Originally Posted by Micron View Post
    At first glance I'd say your problem is that you've compartmentalized what is basically the same data. Seems to me that all that data should be in one table where you have a field to archive it. That could be "Archived" - holding the date that the record was archived. Your approach would require modifications to queries and forms based on changes to data, which is a good indication of improper design.

    So yes to the second part of your post. Use whatever field name/date makes sense to you.
    Thanks. I'll play around with this a bit, but my thought is:

    1) Create a single table called "tblEmployee_Info_Archive"

    Table would include the following fields:
    -UserID
    -Name
    -Job Title
    -Department
    -Supervisor
    -Archive Date

    2) On the 1st of each month, run a query to append the current Employee Information into that table. As part of the query, insert the Month + Year into the Archive Date field

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you are considering transferring data to your archive table, you will face the situation already mentioned of needing additional queries, forms and reports to handle them.

    However, if you are just appending the data to the archive table, you will also be duplicating data which is both unnecessary and poor practice.

    All you need to do is flag your data in the main table with a Boolean field Archived as suggested by micron or using a DateEntered field when a new record is added.
    No archived table is needed based on your description. This will make administration much simpler.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Adviced on Archiving Old Data
    By ManuelLavesa in forum Database Design
    Replies: 8
    Last Post: 03-16-2018, 11:07 AM
  2. Archiving Data by Quarter - Best Solution?
    By warren0127 in forum Access
    Replies: 8
    Last Post: 08-04-2017, 05:53 AM
  3. Archiving Linked Data
    By kevinegg45 in forum Access
    Replies: 3
    Last Post: 01-08-2013, 01:07 PM
  4. Replies: 19
    Last Post: 12-11-2012, 05:51 PM
  5. Find employee, display name, calculate retirement
    By sephiroth2906 in forum Forms
    Replies: 2
    Last Post: 10-24-2011, 02:59 PM

Tags for this Thread

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