historial data

[slimjen1]

All. Using access 2010. I have a database in which an excel spreadsheet is imported on a monthly basis. This spreadsheet takes the place of the prior month import. The prior month is renamed to when it was imported. The problem is; these tables are dating back a couple a years now and I would like to archive the data. Problem is; management wants to be able to go back in time and see prior months as it was imported. Like a snapshot of some sort. I know I can archive the data into one table in the backend but any suggestions on how to get the information as it was imported in at any given time other than going back to each table and putting an identifying mark to identify when it was pulled. There are about 3yrs on a montly basis Thanks

 

[plog]

I would copy one of these files, paste just its structure and use that as the destination for all this data. Next I would add a field to this data which would hold the date that data is from.

If you've got a standard naming convention for all those tables (ImportedData_032012, ImportedData_042012, etc) then you can use some sort of VBA script to import that data into that main table you created.

If not, you've got either 36 copy-and-pastes to do, or 36 APPEND queries to execute manually, updating the ReportDate field value for each different table. Which isn't a bad pennance to pay for keeping your data improperly structured.

 

[slimjen1]

Thanks. I thought about both ways. I do have a standard to write a vba script; just have to figure the script.
Thanks so much!