query with inner join that takes average of related records

[wishkey]

I have two tables [Events] and [Weather]. The event table contains two relevant fields [EventName], [EventDateTime]. The weather table contains [DateTime] and [Temperature].

I want to create a query that displays all events and the weather information for that time. Since events last three hours, I want to take an average of time x, time x+1 and time x+2, where time x = [EventDateTime]. I know how to create an inner join to display the weather at the start time of each event, just don't know how to take average of that and the two following times.

any help would be appreciated. thx

[June7]

This would be done with an aggregate (Totals) GROUP BY query or in a report using Grouping & Sorting features with aggregate calcs in footer.

You only want 3 records averaged? Are there only 3 events each date?

How is the weather relevant?

[wishkey]

Events occur irregularly. From Monday-Thursday there are none and then on weekend there are 2-4 each day. Each event lasts three hours (hence average of three hourly weather observations).

Weather observations are hourly (168 per week).

Basically I would like to find average weather conditions for each event, so that I can observe the relationship between weather and attendance. (does hot weather deter crowds at concerts? Does rain? How much? Given temperature x and rainfall chance y, what crowd can i expect at a given event).

I will test aggregate query and get back to you. Thx

[wishkey]

I still can't figure out how to make it average the three weather observations related to event. If event starts at 2pm, i want to average 2pm, 3pm and 4pm and ignore the other values for the day. Any ideas?

I've attached example database

Cheers

[June7]

Try:

Query1
SELECT [EventID] & "-" & [ID] AS EventWeatherID, Events.EventID, Events.EventName, Events.EventDate, Events.EventDateTime, Weather.ID, Weather.Date1, Weather.DateTime, Weather.[Air Temperature in degrees C]
FROM Weather INNER JOIN Events ON Weather.Date1 = Events.EventDate;

Query2
SELECT Query1.* FROM Query1 WHERE Query1.EventWeatherID IN
(SELECT TOP 3 EventWeatherID FROM Query1 AS Dupe WHERE Dupe.EventID = Query1.EventID ORDER BY Dupe.DateTime DESC, Dupe.EventID)
ORDER BY Query1.EventID;

Query3
SELECT Query2.EventID, Query2.EventName, Query2.EventDate, Avg(Query2.[Air Temperature in degrees C]) AS [AvgOfAir Temperature in degrees C]
FROM Query2
GROUP BY Query2.EventID, Query2.EventName, Query2.EventDate;

Or create report with Query2 as the RecordSource and use report Grouping & Sorting features with aggregate calcs in group footer.