Archiving Employee Data + Querying To Find Changes

[Bcanfield83]

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!

[Micron]

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.

[isladogs]

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

[fletcjas]

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.

[Bcanfield83]

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

[isladogs]

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.