Query Design 2

[Joshy]

I got a tbl that looks like the following:

Time: TemperatureIn: TemperatureOut: ... ... ... ... ... ...
12:15 67 65 ... ... ... ... ... ...
12:30 69 66 ... ... ... ... ... ...
12:45 71 69 ... ... ... ... ... ...
13:00 71 70 ... ... ... ... ... ...
13:15 70 69 ... ... ... ... ... ...
13:30 73 71 ... ... ... ... ... ...
13:45 72 67
14:00 74 70
14:15 72 69
.... ... ...

I want to design a query that calculates the average values for the temperatures for every hour. For example, the output would be :

Time: TemperatureIn TemperatureOut
13:00 69.5 67.0
14:00 ... ... ... ...
... ...

where 69.5 is (67+69+70+71)/4.

what would be the fastest solution? Keep in mind that I have a lot of fields of temperatures (30+).

 

[ghubbell]

Hi Josh, not saying it isn't possible but in your case you really mean you have 30 fields of temperatures? If you do you have a structural problem that's just going to cause you more headaches in the future. Your layout as above is fine. Period. Add 1 field for "ThingwhosTempIamTakingID". "ThingwhosTempIamTakingID" should exist in another table, one to many related to your "ThingwhosTempIamTakingID" field in your existing table. If you set it up as described you will be able to run wizard built simple select queries grouping~summing at rocket speed.

Gord
ghubbell@total.net

 

[Terpsfan]

I'm not sure why you would have 30 fields of Temperatures. Check to see if you could reduce redundancy. Here is a sample query you could use to get the average time for a particular field:
SELECT Avg([TempIn]) As 1200AvgTempIn
FROM tblAVGTIME
WHERE ((([Time]) Between #12:00# And #13:00#));

 

[Joshy]

Well I have 30 fields of temperatures because these data are collected from 30 different temperature sensors in a greenhouse(located at various locations), so I don't think I can reduce redundancy.
Anyhow, its tedious that I have so many columns to work with but that's just the way it is.
Gord, I don't quite follow you. All I'm trying to do is to calulate/output the average temperatures (for every field) for every hour by averaging the 4 data points collected at quarterly intervals.
Omega, obviously your method would work. But it there an alternate solution where you can fiddle with the records to retrieve the hourly averages for each field without creating new expressions, since the amount of data I have is quite large.
Josh

 

[ghubbell]

Hi Josh,
Here's a structure I see would work:

SensorTable
SensorID (P,1-n)
SensorDescription
SensorLocation
Perhaps more info on the sensor

ReadingsTable
SensorID (F)
Time
TempIn
TempOut
Perhaps info like ReadingBy, or Note


P Primary key (AutoNumber)No duplicates. F Foreign key allow duplicates. 1-n = one to many relationship.

Each sensor may have many readings. If you need to query by a specific sensor and/or time or whatever, you would now be able to. Your existing data is not lost. It would just have to be moved carefully. Do a sketch or take a peek in the F1 help on relational database design. Let's know how you'd like to go... but if you start with a bad foundation, the house won't stand for long.
Gord
ghubbell@total.net