Current and Final data tables

[kit_sune]

In your opinion what would be the easiest way to do this?
I have two tables that in essence are identical. The big difference is that one is meant to be a finalized version of the other. For simplicity sake let’s call them “CurrentData” and “FinalData”, and the idea is to import data from an outside source each day. This data is for the current reportable month. It’s possible that some times the data in this outside source could change slightly, so I plan on using a delete query on all data within the CurrentData table, and then reimport the new information into an Import table. From here a query slightly modifies the data into a better format for our products, and then places this data into the CurrentData table.

But now the predicament comes when you consider that upper management wants monthly and weekly totals, and considering that the week often splits into separate months, I need to find a way to pull the data from the FinalData table, and join it with the data from the CurrentData table, within a query, so I can get my totals. I had considered using a couple append queries to put this data into yet another table, but I really don’t want to do this if I can help it, I’ve made things complicated enough.


Any thoughts are appreciated even if you tell me I’m going about the entire process wrong!
Thanks, Kit

 

[Mihail]

My version:
Your tblFinalData should contain a field DataDate of type Date/Time
Import data in a temporary table. Let's name it tblTemporary (can be your CurrentData table)
Use an Append query to append this to the FinalData table tblFinalData.
If the imported records already have a field with dates is OK. If not, manage that your append query to fill the DataDate field with a certain date.
This should be the structure for the tblFinalData:

tblFinalData
ID_FinalData - AutoNumber (PK)
DateData - Date/Time
Other fields

This table is all you need to answer to the upper management questions.
Note that your CurrentData table is NOT necessary for the current reportable month

 

[plog]

My version:

Don't build a 'Final Data' table, build a 'Final Data' query to get the data into the format you need. Change all those UPDATE/APPEND queries in the process to SELECT queries. With this process you load your new data, and run the last query and it holds the data just like you need it--no need to click 10 queries in the proper order to 'build' a new table.