Results 1 to 6 of 6

Archiving Employee Data + Querying To Find Changes

  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    39

    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 online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    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 11:05 AM. Reason: clarification
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  3. #3
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  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
    39
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

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, 10:07 AM
  2. Archiving Data by Quarter - Best Solution?
    By warren0127 in forum Access
    Replies: 8
    Last Post: 08-04-2017, 04:53 AM
  3. Archiving Linked Data
    By kevinegg45 in forum Access
    Replies: 3
    Last Post: 01-08-2013, 12:07 PM
  4. Replies: 19
    Last Post: 12-11-2012, 04:51 PM
  5. Find employee, display name, calculate retirement
    By sephiroth2906 in forum Forms
    Replies: 2
    Last Post: 10-24-2011, 01: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
  •  
Tech Forums: Microsoft Office Forums