Average of a calculated field

[BenH]

I’ve a table (tblFaultCall) with the following fields (example values are also given)​

strCallOwner / John Doe
strCallCategory / MS Word
dtmDateOpen / 01/01/2012 (note GB dates)
dtmDateClosed / 20/02/2012 (note GB dates)​

I want a query to tell me the average time it’s taken to get calls from Open to Closed in the last month, by strCategory. I’ll also want to do something similar by strCallOwner.​

I currently have;​

[LEFT]SELECT tblFaultCall.strCategory, Sum([tblFaultCall]![dtmDateClosed]-[tblFaultCall]![dtmDateOpen]) AS DaysToFix
FROM tblFaultCall
WHERE (((tblFaultCall.dtmDateClosed)>#5/25/2012#))
GROUP BY tblFaultCall.strCategory;
[/LEFT]

The results are grouped properly by cateogry, but it’s summing the DaysToFix and not giving an average.​

If I amend "Sum" to "Avg" it goes horribly wrong.​

Any clues?​

I need to be able to put the SQL in an ASP page.​

Thanks.

 

[MarkK]

Does this help?

Average = Sum / Count

And you have the sum....

 

[BenH]

OK, query fixed

SELECT tblFaultCall.strCategory, Sum(tblFaultCall!dtmDateClosed-tblFaultCall!dtmDateOpen) AS DaysToFix, Count(tblFaultCall.ID) AS CountOfID, [DaysToFix]/Count([ID]) AS AvgDaysToFix
FROM tblFaultCall
WHERE (((tblFaultCall.dtmDateClosed)>#5/25/2012#))
GROUP BY tblFaultCall.strCategory;

However, too many decimal places in the avwerage field. And when pasted into my ASP page, the results in the browser also show too many deciamal places.

Can the SQL be amended to show only two decimal places?

 

[BenH]

It is OK - solution found

Used FormatNumber() in my ASP page