Average Time

[carrietm]

I have a time field, which was imported from an excel spreadsheet.
0:08:38
0:01:47
0:07:50
0:07:50
0:06:52
0:19:00
0:11:59

In excel, I created the average time by entering =AVERAGE(J3:J9)
I've played around with the field properties in the table, in the query and everything that I can think of but I can't figure out how to average the time by "person" in Access.

Go easy on me, I'm NOT an expert user. : - )

[HansV]

Welcome to Eileen's Lounge!

Create a new query in design view, and add the table containing the time field.
Add the field or fields that identify the person to the query grid (for example SSN, EmployeeID, ...)
Also add the time field.
Change the query to a Totals query by clicking the Totals button (the one with the Greek letter Σ) on the toolbar/ribbon.
This will add a Total row to the query which will be set to Group By for all fields. Change the Total option for the time field from Group By to Avg.
Switch to Datasheet view to see the result of the query.

[carrietm]

Thank you for the reply Hans. I'm getting "Data type mismatch in criteria expression."

[HansV]

If you open the table in design view, what is the data type of the time field? Is it Date/Time, or has it become a text field during the import?

[carrietm]

It imported over as a text field, then I changed it to Date/Time.

I guess I'm expecting the original time format. When I run the query I get 0.00627741228070175.
Maybe that is the average but I'm expecting to see the 00:00:00

[HansV]

Did you change the Total option for any other field than the time field? If you leave the other fields as "Group By" it should work (although you'll probably have to set the Format property of the time field to h:mm:ss).

Added: I see that you edited your reply.
Open the query in design view, and click in the time column.
If necessary, activate the Properties window (Access 2003 or before) or Property Sheet (Access 2007 or later).
Enter h:mm:ss in the Format property.
Now switch to Datasheet view.

[carrietm]

Thank you - that is just what I was looking for!