Averages

I have a TAT column built with formula of TAT:
Sum((QL_Production!Completed_Date-QL_Production!Received_Date))
How would I get an average of the that field to show at the bottom of the
report?

On Tue, 7 Jul 2009 16:13:01 -0700, LG wrote:

I have a TAT column built with formula of TAT:
Sum((QL_Production!Completed_Date-QL_Production!Received_Date))
How would I get an average of the that field to show at the bottom of the
report?

Is Received_Date in fact a date field!? If so what's the meaning of the sum?

You can average a field in the report's recordsource query (but not the value
of a textbox on the report) by putting a textbox in the form (or group) footer
with a control source

=Avg([fieldname])
--

John W. Vinson [MVP]

This is something that I took over. Unfortunately the person who built this
had much more knowledge of SQL and built everything in the reports on the
back end with the queries not showing in the objects. I have that field that
was built and works for for the daily Turn around Time but How or where do I
put the column at the end of the report for the average of all the days in
tat column going down? Yes receive_date is an actual date.

"John W. Vinson" wrote:

On Tue, 7 Jul 2009 16:13:01 -0700, LG wrote:

I have a TAT column built with formula of TAT:
Sum((QL_Production!Completed_Date-QL_Production!Received_Date))
How would I get an average of the that field to show at the bottom of the
report?

Is Received_Date in fact a date field!? If so what's the meaning of the sum?

You can average a field in the report's recordsource query (but not the value
of a textbox on the report) by putting a textbox in the form (or group) footer
with a control source

=Avg([fieldname])
--

John W. Vinson [MVP]

Unfortunately, this DB was handed over as is and the person who built it was
way far advanced on the SQL side. The queries are all built behind the
reports but nothing in the objects. The receive date field is an actual
date. Where would I go about putting the AVG of the TAT? Would it go in the
report and if so what would be the steps to get it there? Currently there is
a box that says Avg TAT but it is not averaging.

"John W. Vinson" wrote:

On Tue, 7 Jul 2009 16:13:01 -0700, LG wrote:

I have a TAT column built with formula of TAT:
Sum((QL_Production!Completed_Date-QL_Production!Received_Date))
How would I get an average of the that field to show at the bottom of the
report?

Is Received_Date in fact a date field!? If so what's the meaning of the sum?

You can average a field in the report's recordsource query (but not the value
of a textbox on the report) by putting a textbox in the form (or group) footer
with a control source

=Avg([fieldname])
--

John W. Vinson [MVP]

On Wed, 8 Jul 2009 08:32:02 -0700, LG wrote:

Unfortunately, this DB was handed over as is and the person who built it was
way far advanced on the SQL side. The queries are all built behind the
reports but nothing in the objects. The receive date field is an actual
date. Where would I go about putting the AVG of the TAT? Would it go in the
report and if so what would be the steps to get it there? Currently there is
a box that says Avg TAT but it is not averaging.

You're assumiing that I have a great deal more knowledge about your database
than you have posted; of course I don't!

I have NO idea what a TAT is, anything about the structure of the tables,
where you're putting this box, or what's in that textbox's control source.

Step back a bit, and remember that we're volunteers donating our time, and
answering many different people's questions; and that we cannot see your
screen. Post some information that would help someone in that position
understand the context and the structure of your form, and I'll be glad to try
to help.
--

John W. Vinson [MVP]

Thank you for all your time I do appreciate it.
The TAT was referenced above as the turn around time. The table is QL_Prod
Fields are Proc, Claim_Type, Rec_Date, Completed_Date, and I the added
column is
TAT: Sum((QL_Production!Completed_Date-QL_Production!Received_Date)).
Currently this report produces the processor name, with all the claims by
type, the recieve date, completed date and the TAT (turn around time) for
each. What I need to do from this point is figure out the average of the TAT
for all the records at the end. I did get many 0's as TAT and (2) 6 days and
few others the calculation manually was .67 and the bottom of this report
stated 6.
Is this enough information or is more detailed required?



"John W. Vinson" wrote:

On Wed, 8 Jul 2009 08:32:02 -0700, LG wrote:

Unfortunately, this DB was handed over as is and the person who built it was
way far advanced on the SQL side. The queries are all built behind the
reports but nothing in the objects. The receive date field is an actual
date. Where would I go about putting the AVG of the TAT? Would it go in the
report and if so what would be the steps to get it there? Currently there is
a box that says Avg TAT but it is not averaging.

You're assumiing that I have a great deal more knowledge about your database
than you have posted; of course I don't!

I have NO idea what a TAT is, anything about the structure of the tables,
where you're putting this box, or what's in that textbox's control source.

Step back a bit, and remember that we're volunteers donating our time, and
answering many different people's questions; and that we cannot see your
screen. Post some information that would help someone in that position
understand the context and the structure of your form, and I'll be glad to try
to help.
--

John W. Vinson [MVP]

As the Received_Date column is a date/time data type am I right in assuming
that it is being used to store the duration of the turn around time values,
with each value being less than 24 hours? The date/time data type is not
really intended for storing time durations; it actually stores a point in
time as a 64 bit floating point number with the integer part representing the
days and the fractional part the times of day, as an offset form 30 December
1899 00:00:00. Consequently any value entered as a time will be stored as a
value of zero point something, it not being possible to enter a time of 24
hours or more. So, subject to that constraint it will work after a fashion
for storing a time duration, but is not the ideal method for doing so.

If you sum these date time values you'll get the sum of the underlying
floating point numbers, which will give you the total number of days, and any
remaining part of less than a day as the value after the decimal point. The
summation will be over whatever the query is grouped by.

As you are summing the values in a computed column in the report's underlying
query, not in a computed control in the report, there should be no reason why
you cannot average its values in the report by adding an unbound text box o
the report footer with a ControlSource property of:

=Avg([TAT])

This should give you the average as the number of days, again with any
remaining part of less than a day as the value after the decimal point. If
you want to return this value as hours:minutes:seconds you can do so with the
following function:

Public Function TimeElapsed(dblTotalTime As Double) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

TimeElapsed = lngHours & strMinutesSeconds

End Function

Ken Sheridan
Stafford, England

LG wrote:
This is something that I took over. Unfortunately the person who built this
had much more knowledge of SQL and built everything in the reports on the
back end with the queries not showing in the objects. I have that field that
was built and works for for the daily Turn around Time but How or where do I
put the column at the end of the report for the average of all the days in
tat column going down? Yes receive_date is an actual date.

I have a TAT column built with formula of TAT:
Sum((QL_Production!Completed_Date-QL_Production!Received_Date))
[quoted text clipped - 8 lines]

=Avg([fieldname])

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

Thanks this worked

"KenSheridan via AccessMonster.com" wrote:

As the Received_Date column is a date/time data type am I right in assuming
that it is being used to store the duration of the turn around time values,
with each value being less than 24 hours? The date/time data type is not
really intended for storing time durations; it actually stores a point in
time as a 64 bit floating point number with the integer part representing the
days and the fractional part the times of day, as an offset form 30 December
1899 00:00:00. Consequently any value entered as a time will be stored as a
value of zero point something, it not being possible to enter a time of 24
hours or more. So, subject to that constraint it will work after a fashion
for storing a time duration, but is not the ideal method for doing so.

If you sum these date time values you'll get the sum of the underlying
floating point numbers, which will give you the total number of days, and any
remaining part of less than a day as the value after the decimal point. The
summation will be over whatever the query is grouped by.

As you are summing the values in a computed column in the report's underlying
query, not in a computed control in the report, there should be no reason why
you cannot average its values in the report by adding an unbound text box o
the report footer with a ControlSource property of:

=Avg([TAT])

This should give you the average as the number of days, again with any
remaining part of less than a day as the value after the decimal point. If
you want to return this value as hours:minutes:seconds you can do so with the
following function:

Public Function TimeElapsed(dblTotalTime As Double) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

TimeElapsed = lngHours & strMinutesSeconds

End Function

Ken Sheridan
Stafford, England

LG wrote:
This is something that I took over. Unfortunately the person who built this
had much more knowledge of SQL and built everything in the reports on the
back end with the queries not showing in the objects. I have that field that
was built and works for for the daily Turn around Time but How or where do I
put the column at the end of the report for the average of all the days in
tat column going down? Yes receive_date is an actual date.

I have a TAT column built with formula of TAT:
Sum((QL_Production!Completed_Date-QL_Production!Received_Date))
[quoted text clipped - 8 lines]

=Avg([fieldname])

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1