I work for an electrical company and my current job is to create a load database which keeps track of the amount of watts used at each meter in our commercial electrical meters. I have attached the spreadsheet that will import the data and am curious to know how you think the database should be designed. I am going to take out the fields Src and scale because they are not needed. I am also going to take out the max and average fields because I can calculate them myself in Access. My idea is to have 2 (3 counting the linked table that I will import into and then append from.) tables:
tblMeterPoint:
pkMeterPointID
MP Nbr
Meter Point
tblDay:
pkDayID
fkMeterPointID (from tblMeterPoint)
Day
HE1 (Hour ending 1)
HE2
HE3
HE4
HE5
HE6
....
all the way to HE25 so we can account for daylight savings time.
Then I will have a select query where you can pick the time frame and what dates to grab. The only thing I am scared of is if you can have 25 or more fields in a query because it seems there are limitations.
One way to make another table is to split the tblDay into 2 tables: tblLightLoadHours and tblHighLoadHours, where the first six hours of the day and the last two are light load hours and everything else is high load hours. Not sure if this is neccessary though... Anyways, just checking the architecture of this before I delve into it. Thanks.