How best to design a database to query time series data in different table with common field names

[CellOut]

I am trying to move a large Excel document into MS Access but I have not used Access for some time and would really appreciate some advice as I may be heading off in the wrong direction here or missing something obvious.....

In Excel the data is organised in multiple sheets with each sheet have Time in the first column in 5 minute steps (so best to be a primary key field?) and the heading of the following columns (Fields?) all being a unique item name (in the example below 'ItB' 'ItC' etc). In each of the cells is a number value for each time/item. There are a number of these sheets.

Table1
Time ItB ItC ItD
00:05 1 0 2
00:10 1 1 2
00:15 1 0 2

Table2
Time ItB ItC ItD
00:05 2 1 2
00:10 1 1 2
00:15 1 0 2

Query1 (Table1 x Table2)
Time ItB ItC ItD
00:05 2 0 4
00:10 1 1 4
00:15 1 0 4




There are then separate sheets, with the same column headers (and with the same time stamp) which have a value in each cell based on a calculation from the relevant time/item in two or more sheets (sometimes simple addition or multiplication, sometimes with IF etc). I have put an example 'Query1' showing the relevant values in 'Table1' and 'Table2' multiplied. In reality the number of time slots might run to >100,000 and the number of Items could run into 1,000's (not that it does in Excel currently).

I would assume I create a relationship on 'Time' but can I also somehow link the Field Names so that it is Table1_ItB x Table2_ITB or do I need a table with a unique ID, Time, Item, and Value instead (which then would need two relationship links against Time and Item? and be a lot harder to for me to upload data from my existing sources)

I need to be able to upload data from Excel periodically to add more data to some of the tables (adding extra time slots to the end of the data) which would then create new data on the queries. It is easiest to upload a few single sheets (one for each table). Also, if there is not another way, I would need to make an individual Field in each Query for each item (ItBxItB, ItCxItC.....), I am hoping there is something much easier than this......

Can anyone help point me to an example that would get me started in the right direction?

Many Thanks

[ranman256]

In access you'd append all sheets of all excel files into a single table, tData.
youd also have tables for the IDs: itB, itC, itD.

then you can join the tData to the tID tables.

[CellOut]

I think it is coming back to me now, I can just build queries in SQL instead.....

[CellOut]

Thanks ranman256, I appreciate the speedy reply