If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Averages
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] |
#3
|
|||
|
|||
Averages
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] |
#4
|
|||
|
|||
Averages
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] |
#5
|
|||
|
|||
Averages
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] |
#6
|
|||
|
|||
Averages
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] |
#7
|
|||
|
|||
Averages
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 |
#8
|
|||
|
|||
Averages
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 |
Thread Tools | |
Display Modes | |
|
|