Hourly Temperature Data Max Average...

[joshery420]

So I've been trying to figure this out on my own for some time now, but I'm honestly not very good with Access or VBA, so I'm hoping to get a little bit of involved help from you guys who hopefully know this stuff better than I do!

So, what I'm working with is an Access 2003 Database that contains an Excel spreadsheet titled "Temperature Data". This spreadsheet contains hourly temperature data from multiple temperature probes that was gathered in an arbitrary order each year and appended into the document. e.g. So even though site 2100 may have been checked EVERY year between 2003 and 2010, the listings for site 2100 won't be contiguous in the spreadsheet.

The data in this file has 5 columns: ID (auto-incrementing primary key), Site (number of river-site), Date1 (date probe was measured), Time (hour measured, such as 1:00:01 AM or 12:00:01 PM) and Temperature (in degrees Fahrenheit).

My goal is to take this data and have the max average temperature for each site for each year.
This would require first getting a daily average on each site, since each day a probe is measured we have 24 actual values.
Next I need a rolling 7-day average on each site, returning a Null/0/error or some other clearly invalid data for the first 6 days that each probe is measured each year.
Finally I need to find the max average temperature for each site for each year and display that and it's corresponding date, sorted by ascending date.
So in the end, assuming that site 2100 was measured each year between 2003 and 2010, I would have 8 listings for site 2100, sorted by date, that would each show the year and max average temperature measured that year.

Boy, THAT was a mouthful. XD
So if anyone's willing to give me a bit of help I'd really appreciate it!!
Thanks guys.

 

[DCrake]

As long as you have th raw data at is most detailed point (hourly temp) and you have the date of the temps then it should just be a case of creating several queries grouping by levels of interest.

Level 1 - Actual Temp by Hole
Level 1 Avg - Average actual temp by Date
Level 2 Avg - Level 1 Avg by month and year of date
Level 3 Avg - Level 2 Avg by year of date
Level 4 Avg - Level 3 Avg by Site

 

[gfultz]

Hi Joshery420

I have attached a sample database with a setup like you are requesting. Like DCrake said, you can use any combination of Group By's etc to acheive what you are trying to do.

The sample has one query setup to group by site, by year and then provide the avg temp.