How to calculate average time/duration?

[Singh400]

I was tasked with finding out what is the average time “Int X, Int Y etc etc” did in all of the five weeks in October 2007.

I was able to narrow down most of the records (from a 6500+ record DB) using a simple query.

Gives…

(Coloured blocks represent a single interpreter)

But what I couldn’t figure out what to (only in Access I might add) is how to actual calculate average time for that week.

In the end I “cheated” and export the end results of the query and did the rest in Excel.

But I would like to know how to do it anyway for future reference in Access 2003.

Ideally what I would like is an extra column name Avg which gives the average time of the interpretation sessions for that week for that particular interpreter.

I was sure it was going to be either:

• Totals > Avg
  OR
• Avg([TimeDone])

Both return a different error. Latter says "'Interpreter' is not part of an aggerate function" and formers says "Date type mismatch in criteria expression".

Thank you.

 

[namliam]

Avg([TimeDone]) is more or less right but you have to activate the "Group by" option by clicking the Sigma/sum sign in the menu ....

 

[Singh400]

Avg([TimeDone]) is more or less right but you have to activate the "Group by" option by clicking the Sigma/sum sign in the menu ....

Tried that aswell. Errors out! This is driving me nuts.

/me going nutty

 

[namliam]

Offcourse you have to change the values in the different columns to match what you are trying to do, just clicking that button doesnt fix your problem.

 

[Singh400]

Offcourse you have to change the values in the different columns to match what you are trying to do, just clicking that button doesnt fix your problem.

Sorry mate, you are gonna have to spell it out for me. My brain has turned to mush trying to do this. Every which way I try I always end up with the 2 same error messages.

 

[Singh400]

This should work right?

SELECT Interpreter, AVG(TimeDone)
FROM qryForMack
GROUP BY Interpreter

But that is STILL returning errors. I've tried enclosing (TimeDone) in Square brackets. And it STILL spits an error back at me. I'm going loopy trying to nail this.

I'm "stacking" queries. By that shouldn't be a problem right?

Edit* I know what's causing the errors. A freaking reserved word was used, in one of the fields I'm pulling info from. Damn [End]. Thank god I'm putting a new DB together. Would renaming the field work? This is the new error I'm getting...

The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect.

 

[WayPay]

Would renaming the field work? This is the new error I'm getting...

The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect.

Never seen THAT one before . Putting brackets around the offending column name should work. The problem could be somewhere in the underlying queries and only pop up when a GROUP BY is used, I've seen stranger things . Also, check the [TimeDone] field for non-numeric values (date/time is numeric, too ).

BTW I don't consider getting the right data to Excel and getting things done there cheating. It's just using the right tool for the job. I have lots of reports set up this way by request. Managers like spreadsheets, so they can "play around" with the data. Excel does that way better than Access.

 

[Singh400]

The problem could be somewhere in the underlying queries and only pop up when a GROUP BY is used, I've seen stranger things .

This is what is happening, no idea how to resolve it though. I've renamed the offending fields to DateOA, EndOA, and StartOA. Still getting errors.

Also, check the [TimeDone] field for non-numeric values (date/time is numeric, too ).

[TimeDone] is the field created in the query. It is obtained by [EndOA]-[StartOA]. All of the data contain in those two fields are times, and nothing else. Field format is set to Short Time (eg 17:35).

BTW I don't consider getting the right data to Excel and getting things done there cheating. It's just using the right tool for the job. I have lots of reports set up this way by request. Managers like spreadsheets, so they can "play around" with the data. Excel does that way better than Access.

Cheating was a bad word, I know Access can do it, so why isn't it!

 

[WayPay]

I did a quick web search for that error and found another error about the LEVEL clause, and they had to do with a ; somewhere in the sql statement. Check for that.

To hunt the error down, first check the query you're basing your query on, work your way through all used queries.

 

[Singh400]

I did a quick web search for that error and found another error about the LEVEL clause, and they had to do with a ; somewhere in the sql statement. Check for that.

To hunt the error down, first check the query you're basing your query on, work your way through all used queries.

The query is fine. Here is the SQL for that query...

SELECT Bookings.Interpreter, Bookings.DateOA, Format([EndOA]-[StartOA],"hh:mm:ss") AS TimeDone
FROM Bookings
WHERE (((Bookings.Interpreter)<>"Agency A" And (Bookings.Interpreter)<>"Agency B" And (Bookings.Interpreter)<>"Agency C" And (Bookings.Interpreter)<>"Agency D" And (Bookings.Interpreter)<>"Int A" And (Bookings.Interpreter)<>"Int B" And (Bookings.Interpreter)<>"Int C" And (Bookings.Interpreter)<>"Int D" And (Bookings.Interpreter)<>"Int E" And (Bookings.Interpreter)<>"Int F" And (Bookings.Interpreter)<>"Int G") AND ((Bookings.DateOA) Between #10/1/2007# And #10/5/2007#) AND ((Format([EndOA]-[StartOA],"hh:mm:ss"))<>"" And (Format([EndOA]-[StartOA],"hh:mm:ss"))<>'00:00:00'))
ORDER BY Bookings.Interpreter, Bookings.DateOA;

And that spits out what I want to a degree (show in OP, image 2). It's only when I attempt to add something to that SQL statement do things start to get weird.

There's only one query I'm using and that is the one above. It's pulling information from table Bookings.

I'll have another stab at it at work. I'm getting nowhere, just going round in circles

 

[WayPay]

Indeed, nothing looks wrong. You might want to have a look at this. Makes spotting errors in big SQL statements a breeze .

 

[Singh400]

Indeed, nothing looks wrong. You might want to have a look at this. Makes spotting errors in big SQL statements a breeze .

Just dumped in the current working SQL statement and it's picked up no errors. Will have a play tomorrow at work too.

Thanks for that link

Doesn't look like this is possible to be honest, or atleast not with Time.

 

[namliam]

Format([EndOA]-[StartOA],"hh:mm:ss") AS TimeDone

Format returns a textstring, not a time value or number which you can average... either use [EndOA]-[StartOA] without the formatting or use TimeValue funtion to get the string back to a number/timevalue which you can average.

That in itself is probably not cause of all these here errors, but you will run into this.

 

[WayPay]

Thanks mailman, a very good point . You're absolutely right, the formatting should be done last. Won't help with that "LEVEL clause" error, though .

 

[Singh400]

I finally did it - haven't opened Access all day. I was fed up of it, and I've only just right now did it.

First big clue was namliam info about FORMAT returning a text string, and not a Date/Time value as I thought it did. This is what was royally buggering me up the arse.

So my 3rd query column now looks like:

TimeDone: [EndOA]-[StartOA]

This just returns a whole series of numbers, like 0.254784112587.

I then took this and created a report. In which I added the field (to the header):

=Avg([TimeDone])

This then returns the avg again in long sequence numbers. I then formatted this field to Long Time format in the Property Sheet. And volia! Everything matches up with my Excel sheet. I checked every one.

I would like to thank you both namliam and WayPay. Nice one lads You both rock, and reps added.

 

[namliam]

FYI

Those decimal numbers like you discovered are also times... A date/time field is simply a double that is displayed as time.
0.25 = 06:00
0.50 = 12:00
0.75 = 18:00
1.00 = 00:00, but on day #2
Etc....

 

[Singh400]

Yeah thats what I thought. Thanks for confirming

 

[NormFY2]

A bit fiddly this one - I had a sum of durations presented from the query, so I used the Format function to get the hours and minutes part separately.

Converted it all into seconds - (Magic Numbers are 3600 times hours, 60 times minutes), then divided the (CInt converted the Format strings to integers) by 60 to give the result in minutes - thinking about it I could have done that more simply by just using minutes - ho hum...

...and then format the entire answer to 2 decimal places, adding " minutes" to the end of the expression.

Here it is :
=Format(((CInt(Format([sumofcalllength],"hh"))*3600+CInt(Format([sumofcalllength],"nn"))*60)/[countofcallerID]/60),"Fixed") & " minutes"

Looking at the code, I used "0.00" and Access has replaced that with the word "Fixed".

 

[The_Doc_Man]

NormFY2... you DO realize you just updated a thread that was 6+ years old?

 

[NormFY2]

err... it's a feature of access I was forced to get to grips with yesterday... just thought I'd share ... and now the thread is current!