459,365 Members | 1,360 Online 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 