If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create a report to get the average time spent on an issue.
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! |
#2
|
|||
|
|||
Create a report to get the average time spent on an issue.
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 wrote: 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! |
#3
|
|||
|
|||
Create a report to get the average time spent on an issue.
Brilliant! Thank you very much.
|
Thread Tools | |
Display Modes | |
|
|