Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Query Design

Query Design

Query Design

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+).

RE: Query Design

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.  :)


RE: Query Design

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
WHERE ((([Time]) Between #12:00# And #13:00#));

RE: Query Design

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.

RE: Query Design

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

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

SensorID (F)
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.  


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close