I have about 1500 spreadsheets that have the same format. The headers are basically a Location, Date, and then the subsequent headers are 1, 2, 3...24 for each hour of the day and below that there is a price at each location (10000 locations) which is different every hour.
Location Date 1 2 3 .... 24
Houston 2-16-12 $25 $30 $40 $20
Dallas 2-16-12 $20 $25 $50 $10
.
.
10000 rows down of data
For each day there are 2 of these spreadsheets. Why are there two? Because there are 2 sets of prices for each city. One price is established the day before and one price is established day of. Each sheet has the same cities but the prices will be the different for the same given date and hour. The second sheet for 2/16 is seen below for example
Location Date 1 2 3 .... 24
Houston 2-16-12 $50 $15 $20 $35
Dallas 2-16-12 $0 $40 $15 $25
.
.
10000 rows down of data
My goal is to bring all of these spreadsheets into Access so I can look at spreads between the prices for a specific city on a specific hour.
I am not sure if it's supposed to be one Access file with 3000 tables in it (1500 days of files times 2 sheets for each day). Will all that even fit (bytes wise) in one Access file? I am not sure what to make the Primary Key whether it's the cities, or the date (02-16-2012), or the date and hour appended together (02-16-2012-01 through 02-16-2012-24) as that is unique from day to day. What should the relationships be? Should I link the mother files from Excel into Access or hardcode them in? Is there an easy way to get 3000 Excel files into Access?
This will definitely require some math and statistical analysis but for right now I just need some structural help as I am new to databasing and the books I have read don't exactly give an example as such.
Thank you for reading and my hope is that these are simple questions for someone with some database and Access experience. I guess more importantly, can this all be done in Access? I appreciate it.
If I wasn't clear or if you have further questions let me know.
Jimmy