Merging changing excel forecasts into one access table??

[snoopydoopy]

hi. Say you have multiple excel sheets where forecasting is done daily. For example, sheet one headings: Depot, department, location, 01/09/10, 02/09,10, 03/09/10 and then second sheet is similar but: Depot, department, location, 02/09/10, 03/09/10, 04,09, 10 - as you can see each day the forecast starts a day after and ends one date late (14 days each in the real one)... how to I join all these forecasts into one table; vertically with each forcast identified by the day it start for analysis later.. rather than doing cross-tabulate which would take ages, I just need to keep adding more excel sheets but the headings change as they are dates.. confused

 

[snoopydoopy]

Let me emphasise however. I have 20,000 products or rows in each excel file and it'll end up being a gig excel file which would be inefficient if not impossible.
Hence I need access database.
So as you can imagine I have 50 days of forecasts; each done daily.
So first few column headings are the same but then the heading! Starts from 01/09 to columns 13/10
Then another forecast is done for next day..
From 02/09 to 14/10..
Next workbook has 03/09 to 15/10
And so on...
Hence you can imagine it going diagonally..
Purpose is to see the significant differences we see each day with our forecasts...
So I need them into one table..
Simply make table and append doesn't work as the headings are dates which change daily...
Pls help anyone...

 

[JHB]

After you've it in MS-Access, would you still use Excel for the daily forecasting or would you do it in MS-Access?
Show how you would like you table to be, field names etc.

 

[snoopydoopy]

Daily forecasting is still done in excel (by separate BI department) but will be needed to added to access continuously...

 

[JHB]

Show how you would like you table to be, field names etc.

Daily forecasting is still done in excel (by separate BI department) but will be needed to added to access continuously...

Then it is only one Excel sheet at daily bases or ?

 

[snoopydoopy]

Yeah its one forecast daily from now, but I started this week so have 50+ forecasts for the last 50 days..

 

[snoopydoopy]

Headings are:
SUPPLIER NAME, DEPOT, RETAIL LINE,NSL,NSL DESCRIPTION,CASE SIZE,Retail Life,DOD,<>,19/09/2014, 20/09/2014,21/09/201422/09/201423/09/201424/09/201425/09/2014

as I said the dates change by one each day... and I need to identify each upload..for analysis later..

 

[JHB]

Headings are:
SUPPLIER NAME, DEPOT, RETAIL LINE,NSL,NSL DESCRIPTION,CASE SIZE,Retail Life,DOD,<>,19/09/2014, 20/09/2014,21/09/201422/09/201423/09/201424/09/201425/09/2014
...
as I said the dates change by one each day... and I need to identify each upload..for analysis later..

If the above is how you would like you table, then you problem starts here, then in a database you need the data to be vertical, not horizontally.
So your "Main" table must consist of fields where the data are "static" (does not change) and only need to be once in your database.
Your "Sub" table consist of fields where the data are "dynamic", like you forecasting data, where you more as one set data for each entry in your "Main" table.
Then link them together with an ID number.
Below is a link how to define relationship between tables:
http://support2.microsoft.com/kb/304466