I'm new to Acess but I'm finding great success in using the program. Now that I understand how to write expressions, I need to find out how to average the results of these expressions. Specifically, I have an expression that will show me the turn around time between an employees hire date and the time they assume thier first field assignment. The expression I am using is: =DateDiff("d",[Date of Hire],[Date of Assignment]
In the report, it will show me the time lapse (in days) for hundreds of individual field staff. What I would like to do is calculate the average time lapse, the minimum time lapse and the maximum time lapse between hire and first assignment for these field staff.
Can anyone out there direct me on how I can do this?
I'm going to assume that your calculation is in a text box on your report's details section. Let's give the textbox containing this information a nice name for use in this example: txtTurnAround (?)
This can be assigned in the properties of the object.
In the report footer, add a new textbox and call it txtAverageTurnAround
In this textbox try this expression:
Repeat for =Max() and =Min()
Please note that I have not tried or tested any of this - so chances are it won't work But it should be a solid start!
I'm thinking that the report will not do this for a field on the report, which means if this does not work, then try this in the report footer.
=Avg(DateDiff("d",[Date of Hire],[Date of Assignment]) and again the same thing for both Min and Max. You might have to play with the Running Sum property under the Data tab for this text box. I'm thinking you might have to change it to Over All.
Mine in not tested either but is where I would go if georgev's suggestion does not work.
Alternatively, if you have an expression in your query which works out the date difference, you can write a second query based on the first and turn totals on (click the summation sign or go to 'View' > 'Totals').
The rest is fairly self-explainatory