By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

"Average" field in a query/report

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.