Average Time

[54.69.6d.20]

Okay. I've got a form that is pulling data off of a query. The First textbox is
LineItem - shows the tool number
AC - Shows a date and time of the lineItem shown
AQ - Shows a date and time of the lineItem shown
C - Shows a date and time of the lineItem shown

How can I find out the average time in each department (AC,AQ and C)?

(line from the query)
LineItemID 123report30Days-AC.DateTimePlaced 123report30Days-AQ.DateTimePlaced 123report30Days-C.DateTimePlaced
138888 5/7/2012 3:50:39 PM 5/8/2012 9:50:57 AM 5/8/2012 3:23:00 PM

 

[Beetle]

You appear to have only one DateTime field for each department. If a product can only be in one department at a time, then you can logically derive the time it left the AC department by looking at the time it entered the AQ department. Likewise for AQ by looking at C. But what about C? That appears to be the last "department", and there is no ending value, so no way to derive any period of time.

Is there something in your structure that you have left out of your post? What are we missing?

 

[54.69.6d.20]

I was told that I only need to show the time pierod between AC to AQ and AQ to C. So they can see in a report how long it took a lineItem in each department.

How long was A in AC before going to AQ (Awaiting Calibration, Awaiting QA) and how long in AQ before going to C (Complete)

So If I can somehow find that. I could make a report showing a 30 day, 90 day and year end report. While showing if a line item went over the 5 day turn around. lol.. I know... It's fun..

 

[Beetle]

At this link you will find a function that can display the difference between two date values in days-hours-minutes-seconds (or other formats if you wish). You can copy this function to a global module then use it in your query to return the elapsed time in the AC and AQ departments for each row. Then you can create another query, form or report that would average these values.

 

[Beetle]

Actually, I have to correct myself here. The function in that link will work for each row, but it returns a string so you won't be able to average the values it returns. If you want granularity in your average to be to the second, then you'll need a field that returns the difference in seconds for each row. You can use the built in DateDiff function for this. You'll then need to return the avererage in seconds based on this column, then convert that to days, hours, etc. You should be able to find a function on the www that does this, just google it.