Our production database holds eight weeks of historical information that get updated in real time. I use an access database to retain a true historical record beyond the eight weeks available in production for each day. I have a very convoluted way of maintaining the historical information:
1) I extract the raw data from the system and do a destructive load into a staging table
2) I add attributes to the raw data (e.g. converting two-letter codes to English) using a query and reference tables
3) I use another query to create a staging table with this data
4) I manually create primary keys on a handful of non-numeric fields and append the 'old' history table to this new staging table (in effect becoming the 'new' table of record); the historical data for the past eight weeks gets overwritten with new, 'updated' data and the older records get recorded, too
5) I use a query to delete the contents of the 'old' history table
6) I use a query to append the staging table into the blank 'old' history table and I now have a 'new' table of record that I use for reporting, etc.
I repeat this process every day. This daily process adds new rows for the latest information and it overwrites/updates a few records from the previous eight weeks. I had two questions:
-Is this the best way to maintain a history table where recent historical records may be changed in our production environment?
-Is there a way I can automate Step 4 where I am manually creating multiple primary key fields?
Any help will be greatly appreciated!
1) I extract the raw data from the system and do a destructive load into a staging table
2) I add attributes to the raw data (e.g. converting two-letter codes to English) using a query and reference tables
3) I use another query to create a staging table with this data
4) I manually create primary keys on a handful of non-numeric fields and append the 'old' history table to this new staging table (in effect becoming the 'new' table of record); the historical data for the past eight weeks gets overwritten with new, 'updated' data and the older records get recorded, too
5) I use a query to delete the contents of the 'old' history table
6) I use a query to append the staging table into the blank 'old' history table and I now have a 'new' table of record that I use for reporting, etc.
I repeat this process every day. This daily process adds new rows for the latest information and it overwrites/updates a few records from the previous eight weeks. I had two questions:
-Is this the best way to maintain a history table where recent historical records may be changed in our production environment?
-Is there a way I can automate Step 4 where I am manually creating multiple primary key fields?
Any help will be greatly appreciated!
