Query average by date and time in MS Access

[wetsprockets]

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

[MMcCarthy]

You need a relationship between WindVector and DOH_FC88to07 other than Date and Time. Have a look at this tutorial on Database Normalisation.