Calculating/Storing Elapsed Time for Future Averaging

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mariew - 31 Aug 2006 01:34 GMT

Please have patience for a true novice here . . . I have read through so many
threads and other suggestions on calculating elapsed time, but still have
some issues. Specifically, I'm using a form to automatically calculate &
store "Time Opened" and "Time Closed" for trouble tickets. (It actually
seems to be working and storing the correct info in the underlying table!)

I created a query to calculate the elapsed time between the two fields in
the table: Time to Resolution: [DTS Activities]![Time Closed]-[DTS
Activities]![Time Opened]. The result displayed is in the following format:
0.0010300. . .

I would also like to calculate an average length of time each trouble
tickets was open. Can I do this in the same query, and, if so, what
expression would I use?

The final question would be how do I store this data in the underlying
table, so I can generate a report from it? Or should I just use the Query to
pull the data from for the report?

Thank you for any assistance you can provide me!!

Reply to this Message

Lynn Trapp - 31 Aug 2006 14:08 GMT

The function for Average should get what you want, but you will need to
generate that in your report footer, rather than in the query --
Average([DTS Activities]![Time Closed]-[DTS Activities]![Time Opened]).
Also, you should not need to store this value. Simply use the query as the
record source for your report.

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

> Please have patience for a true novice here . . . I have read through so
> many
[quoted text clipped - 19 lines]
>
> Thank you for any assistance you can provide me!!

Reply to this Message

John Spencer - 31 Aug 2006 15:26 GMT

I would use DateDiff("n",[TimeOpen],[TimeClosed]) to calculate the number of
elapsed minutes.

You can use the Avg function in a Totals query to get the average minutes.

Avg(DateDiff("n",[TimeOpen],[TimeClosed]) ) as AverageMinutes

Usually it is best NOT to store derived data if you can calculate it.
Trouble with storing the data if you can derive it is you must recalculate
it if any of the values used to calculate the derived data changes.

> Please have patience for a true novice here . . . I have read through so
> many
[quoted text clipped - 19 lines]
>
> Thank you for any assistance you can provide me!!

Reply to this Message