471,052 Members | 1,170 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,052 software developers and data experts.

"Average" field in a query/report

I currently have a query calculating the gap in days between two
dates. The fields being "DateLookedAt" and "DateResolved" plus the
calculated field "TimeTaken".

I am looking to add another field to my report (or does this have to
be created in the query?) that calculates the average of all the data
in the "Time Taken" field.

SQL code or some tips would be much appreciated.

Many Thanks
Nov 12 '05 #1
1 8266
It is not any problem to calculate averages on dates, as dates are stored as
datevalues. But the result will be displayed as date value - not in date
format.
But I guess that your field "Time Taken" is a field with numeric value.
Anyway this formula in your query for the report should work:
Avg(TimeTaken),
And yes put it in your query. Reports are good for layouts, and queries are
good for calculations.
To calculate different between to dates or times, use the DateDiff function.
It returns a numeric long integer whatever you asks it to do. Here the
difference is in days, but you can calculate in everything from years to
seconds.
DateDiff("d",[DeliveriLine]![PlGI date],[DeliveriLine]![AcGI date])

You might run into another problem: You wan't to calculate the difference in
workingdays - leaving out saturdays and sundays. In Microsoft Excel you
will find in an add on library the function NetWorkDays. To add to your
frustration: A similiar function is not available in Access and has to be
created as a user defined function.

Brgds
Rolfern
"Simon Bailey" <go*********@hotmail.com> wrote in message
news:1c*************************@posting.google.co m...
I currently have a query calculating the gap in days between two
dates. The fields being "DateLookedAt" and "DateResolved" plus the
calculated field "TimeTaken".

I am looking to add another field to my report (or does this have to
be created in the query?) that calculates the average of all the data
in the "Time Taken" field.

SQL code or some tips would be much appreciated.

Many Thanks

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Lorem Ipsum | last post: by
46 posts views Thread by TTroy | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.