Create a report to get the average time spent on an issue.

[Gluefoot]

Hi everyone, I'm making the move from Excel to Access. I haven't used MS
Access in about 10 years so I'm a little rusty.

Right now I have a database that has the following..

USER | TASK | IN_DATE_TIME | OUT_DATE_TIME

I'd like to create a report where I could select a task, and a date range
and it would give me the average time spent on that task for the date range
that I specify. The date range would relate to the IN_DATE_TIME column from
the table.

I'm stuck! Could someone please point me in the right direction?
Thanks in advance!

 

[John Spencer]

Sample query would look like the following

SELECT Task
Avg(DateDiff("n",In_Date_Time,Out_Date_Time)) as AverageMinutes
FROM SomeTable
WHERE In_Date_Time >= #2008-01-01# and In_Date_Time <#2008-02-01#
AND Task = "Clean-up"
GROUP BY Task

To build the query in design view
== add the table
== add the task field and the In_Date_Time field to the fields to display
== create a calculated field using the DateDiff function - type the following
DateDiff("n",In_Date_Time,Out_Date_Time)
== select View: Totals from the menu
== Change GROUP BY to Avg under the calculated field
== Change GROUP BY to WHERE under In_Date_Time and input your date range in
the criteria. Since your field contains both a date and a time you will need
to use criteria similar to the WHERE clause above. That is add one day to the
end of your period.

= #2008-01-01# and <#2008-02-01#

Use that query as the source for your report. Once that is working you can go
back and modify it to allow you to specify dates and tasks with parameters.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Gluefoot]

Brilliant! Thank you very much.