By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,365 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,365 IT Pros & Developers. It's quick & easy.

Query average by date and time in MS Access

P: 1
Hello,

I have two tables, WindVector (wind data) and DOH_FC88to07 (water quality samples). The WindVector table has the following fields:

Date (short date)
Time (short time)
WindSpeed
WindDir
WindDirRads
V
U

The DOH_FC88to07 table has the following fields:

Station
Date (short date)
Time (short time)
FC

I am able to successfully calculate the average wind speed and direction for a given (user defined) date and time range (see SQL below)

SELECT Avg(WindVector.V) AS VAvg, Avg(WindVector.U) AS UAvg, Sqr(([VAvg]^2)+([UAvg]^2)) AS R, atan2((-[VAvg]),(-[UAvg]))*57.29577951 AS Wdir
FROM WindVector
WHERE (((WindVector.Time) Between [Forms]![frmParameters]![WindStartTime] And [Forms]![frmParameters]![WindEndTime]) AND ((WindVector.DATE)=[Forms]![frmParameters]![SampleDate]));


What I'd like to do now is calculate the average wind speed and direction for a user defined time period (e.g. four hours) preceding a water sampling event. There are multiple sampling events on the same day but at different times.

Here's what I've tried thus far...but the query doesn't return any results.

SELECT DOH_FC88to07.Station, DOH_FC88to07.Date, DOH_FC88to07.Time, DOH_FC88to07.FC, Avg(windvector.V) AS VAvg, Avg(windvector.U) AS UAvg, Sqr(([VAvg]^2)+([UAvg]^2)) AS R, atan2((-[VAvg]),(-[UAvg]))*57.29577951 AS Wdir
FROM windvector, DOH_FC88to07
WHERE (((windvector.DATE)=[DOH_FC88to07]![Date]) AND ((windvector.Time) Between DateAdd("h",-4,[DOH_FC88to07].[time]) And [DOH_FC88to07].[Time]))
GROUP BY DOH_FC88to07.Station, DOH_FC88to07.Date, DOH_FC88to07.Time, DOH_FC88to07.FC;


Any help would be greatly appreciated (this is fairly urgent)

MS Access 2003
Windows XP Pro Version 2002 Service Pack 2
Jun 19 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
You need a relationship between WindVector and DOH_FC88to07 other than Date and Time. Have a look at this tutorial on Database Normalisation.
Jun 20 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.