I am looking for advice on how to handle this data. I use a SQL database that is provided to our program for annual reporting. This database application has limited reporting capability and I do not have access to the back end data.
I export reports from this database into Access, where I process data for reports monthly, quarterly, annually.
I also get data regularly that I manipulate in Access to input to the SQL database via it's import program.
My issues:
1. Do I need to keep "old" data in these Access databases? For ex, in December, do I keep October's data in the Access database? My reasons for doing so is if I have to compare to previous reports, I have the data at hand, and I know that it hasn't changed. We do enter data as we get it, so I could have October data entered in November, so re-running the report in December could be different.
2. I also keep the old data as a way of logging what I have done - especially on the import side. I think there should be another way to do this - ie create a log table -- Is that a good approach?
3. I have created several different databases for different data sources - for the import side, mostly. This mostly because the data comes from different sources (housing data versus pharmacy data, for ex), and thus needs slightly different processing. This also let me keep track of what I have processed. The work is similar, and the end result is the same - the data ends up in the same 2 tables for the import program.
I would like ideas on how to be more efficient with this. I hope I am clear!
Thanks
Helena
I export reports from this database into Access, where I process data for reports monthly, quarterly, annually.
I also get data regularly that I manipulate in Access to input to the SQL database via it's import program.
My issues:
1. Do I need to keep "old" data in these Access databases? For ex, in December, do I keep October's data in the Access database? My reasons for doing so is if I have to compare to previous reports, I have the data at hand, and I know that it hasn't changed. We do enter data as we get it, so I could have October data entered in November, so re-running the report in December could be different.
2. I also keep the old data as a way of logging what I have done - especially on the import side. I think there should be another way to do this - ie create a log table -- Is that a good approach?
3. I have created several different databases for different data sources - for the import side, mostly. This mostly because the data comes from different sources (housing data versus pharmacy data, for ex), and thus needs slightly different processing. This also let me keep track of what I have processed. The work is similar, and the end result is the same - the data ends up in the same 2 tables for the import program.
I would like ideas on how to be more efficient with this. I hope I am clear!
Thanks
Helena