DateDiff & Averages 1

[Tezdread]

Lo all,

I'm trying to create a query that will give me the 'Average' time it took to close a case based on the dates from [open date] and [closed date]

I've got the DateDiff working (guess that's the easy part) but how can I incorporate the Average to get the value required?

Hope this isn't to complex ;-/

Tezdread
"With every solution comes a new problem"

 

[PHV]

Have you tried to simply use the AVG aggregate function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lupins46]

Just put your datediff formula as the only column in a new query and convert the query to a Totals query. Change the group By to Avg.

 

[Tezdread]

oh really...that complicated ;-) Doh...I tried adding a new field to the same query and had this as the Avg as this seemed to be the logical option but didn't think to run another query from this one...

thanks guys

Tezdread
"With every solution comes a new problem"

 

[Tezdread]

Adding on to the Avg query: What would be the best way to find out the average age of open cases, from the current date but only for that month?

It's 31st March it will count back 31 instead of 28 if it's the 28th Feb

I've seen in another post some way of counting back based on the month but couldn't understand how to implement it in this query?

Tezdread
"With every solution comes a new problem"

 

[PHV]

Your days count:
Day(DateSerial(Year(Now),1+Month(Now),0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tezdread]

thanks PHV, I'm not sure where to put it but I entered it in the criteria area of the first query but when the results were shown they weren't what I was expecting?

I have two queries, the first has this in the first column:
DaysBetween: DateDiff("y",[Date In],[Date Resolved])

The second column has Date In as the field then this as the criteria:
Between ["Date In"] And ["Date Resolved"]

The third column has the Date Resolved as the field

The second query pulls DaysBetween from the first query and provides the average.

This all seems to work ok but when I remove the "Between" criteria, and add Day(DateSerial(Year(Now),1+Month(Now),0)) the results change and look like they're only displaying those records that were resolved in a 31 day period but for all dates?

Tezdread
"With every solution comes a new problem"