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!