Daily Average of last x number of days

[510sx]

This may be a good problem for the experts here.

I am trying to write a query in Access to populate daily average temperature of the pass few days (the date range has to be dynamic).

Lets say I have a table storing the temperature every hour and I want to know each day's average.

any suggestions?

 

[Galaxiom]

Turn on Totals in the Query Design.
Use Group By on the date and location fields and Average on the temperature.
This will give you daily averages by date and location.

For averaging several days build a similar query based on this first one.
Untick Show on the Date field and add a criteria to limit to the dates of interest.

 

[510sx]

Turn on Totals in the Query Design.
Use Group By on the date and location fields and Average on the temperature.
This will give you daily averages by date and location.

I've tried that, but it returned all the records. Then I deleted the dateTime field, it returned the avg of the entire temperature record.

I am expecting to see one average temperature per day.

 

[Galaxiom]

Didn't realise the field included time.
Need to derive a field with just the date.

Dates are actually numbers. Time is the fraction of a day.
Int() returns the integer value from a number.

To just return the date:

Format(Int([datetimefield]), "Short Date")

Group by this field to return data for each day.

 

[namliam]

Just Int(datetimefield), without the format would do it as well...

 

[Galaxiom]

Just Int(datetimefield), without the format would do it as well...

The Format can be omitted if the result is used to update a date formatted field which is not what should be done in this situation.
In a query the Format is required or the result will be displayed as an integer. Today would show as 40032.