Average Time in Query

[skwilliams]

I have a query with the fields dtDate (Date/Time in Short Date format), NC "Number of Chats" (Long Integer), and TCT "Total Chat Time"(Date/Time in hh:nn:ss format) from tblChats. Each date will have multiple NC and TCT values. This query is totaling them by date with the following SQL.

SELECT DISTINCTROW tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') AS ChatDate, Sum(tblChat.TCT) AS SumOfTCT, Sum(tblChat.NC) AS SumOfNC
FROM tblChat
GROUP BY tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date')
HAVING (((tblChat.dtDate)>=Date()-7))
ORDER BY tblChat.dtDate;

The query works fine. Now my question.

I would like the query to figure the average time per chat for each day.

Could this be done without VBA? Any ideas??

Thanks.

 

[ScottGem]

make a new query based on this one one and add a column:

AvgChat: [SumofTCT] / [SumOfNC]

 

[skwilliams]

Thanks.

That worked.

 

[ScottGem]

glad to assist