Hourly Averages

[~~NRH~~]

I am new to Access and I am currently using the 2003 version.

I have imported a database from Excel. It consists of the record of every
vehicle passing a survey location for a week (~60000 vehicles). Each record
contains the date and time (in seperate fields) and it's speed. I would like
to work out the hourly average speed.

I have tired using the simple query wizard but I am battling. My biggest
problem is that I can't group the data hourly.

Any ideas?

 

[S.Clark]

Format([DateField], "hh")

 

[Steve]

Your description of what you want is not clear! Do you want the average
speed for each hour of each day or do you want the average speed of all
vehicles for each hour independent of the day?

Steve
(e-mail address removed)

 

[John Spencer]

It kind of depends on whether you want the average each day and each hour (7 x
24 averages) or just for each hour in the week (24 averages)

You can group by
Hour([YourTimeField])
and by
WeekDay([YourDateField])

If you want the average per hour by day group by both expressions.
If you want the average per hour regardless of the day group by just the first
expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County