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
|
|||
|
|||
Average Days
I'm using the expression below to figure out the Average Number of days in
custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#2
|
|||
|
|||
Average Days
The Null value is cause the problem. You can use the Nz function to prevent
this. When you use the Nz function, you must put each element of the calculation that could possibly be null in a function, not the results for example, assume x = Null Nz(x + 10, 0) will return 0 Nz(x, 0) + 10 will return 10 =Avg(Nz([# of days in Custody],0)) BTW, # of days in Custody breaks almost every naming rule there is. Use only letters, numbers, and the underscore in names. Do not use spaces or any special character other than the underscore. Do not use any Access reserved words (Date, Value, Description, etc) Typical database naming stanards dictate all upper case with words separated with the underscore, so a good name would be: DAYS_IN_CUSTODY -- Dave Hargis, Microsoft Access MVP "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#3
|
|||
|
|||
Average Days
Hi Nick
The nulls won't be included in the average so they won't cause the zero although you should decide whether to convert the nulls to zeroes themselves if you want them included in the average calculation. Eg Avg of null, 3 and 9 = 12 / 2 = 6 Avg of 0, 3 and 9 = 12 / 3 = 4 Is it possible that the Avg is very small and you are seeing it rounded? Try changing the format so you can see decimal places. Also, for testing only, add a Sum of Days in Custody and a Count of Days in Custody This should show you what numbers the Avg calculation is using. Hope this helps Andy Hull "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#4
|
|||
|
|||
Average Days
I'm still getting 0? When I do Sum I get 0? When I do Count I get 2 = # of
records with non-Null values. I also appreciate the additional info. Thanks. "Klatuu" wrote: The Null value is cause the problem. You can use the Nz function to prevent this. When you use the Nz function, you must put each element of the calculation that could possibly be null in a function, not the results for example, assume x = Null Nz(x + 10, 0) will return 0 Nz(x, 0) + 10 will return 10 =Avg(Nz([# of days in Custody],0)) BTW, # of days in Custody breaks almost every naming rule there is. Use only letters, numbers, and the underscore in names. Do not use spaces or any special character other than the underscore. Do not use any Access reserved words (Date, Value, Description, etc) Typical database naming stanards dictate all upper case with words separated with the underscore, so a good name would be: DAYS_IN_CUSTODY -- Dave Hargis, Microsoft Access MVP "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#5
|
|||
|
|||
Average Days
When I do Sum I get 0? When I do Count I get 2 = # of records with non-Null
values. When I do =Avg(Nz([# of days in Custody],0)) I also get 0? I appreciate your time. Thanks. "Andy Hull" wrote: Hi Nick The nulls won't be included in the average so they won't cause the zero although you should decide whether to convert the nulls to zeroes themselves if you want them included in the average calculation. Eg Avg of null, 3 and 9 = 12 / 2 = 6 Avg of 0, 3 and 9 = 12 / 3 = 4 Is it possible that the Avg is very small and you are seeing it rounded? Try changing the format so you can see decimal places. Also, for testing only, add a Sum of Days in Custody and a Count of Days in Custody This should show you what numbers the Avg calculation is using. Hope this helps Andy Hull "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#6
|
|||
|
|||
Average Days
Pardon me, but the aggregate functions ignore nulls in the calculations. So
I doubt that having null in a field will break the calculation of an average. Avg([Some Field]) should return a number or null if all the field values that are being aggregated are null. What type of field or value is [# of days in Custody]? Is it a field from a table or is it a calculation? If it is a calculated column in a query has it been formatted or does it use NZ in the query to ensure it returns a value? -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Klatuu" wrote in message ... The Null value is cause the problem. You can use the Nz function to prevent this. When you use the Nz function, you must put each element of the calculation that could possibly be null in a function, not the results for example, assume x = Null Nz(x + 10, 0) will return 0 Nz(x, 0) + 10 will return 10 =Avg(Nz([# of days in Custody],0)) BTW, # of days in Custody breaks almost every naming rule there is. Use only letters, numbers, and the underscore in names. Do not use spaces or any special character other than the underscore. Do not use any Access reserved words (Date, Value, Description, etc) Typical database naming stanards dictate all upper case with words separated with the underscore, so a good name would be: DAYS_IN_CUSTODY -- Dave Hargis, Microsoft Access MVP "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#7
|
|||
|
|||
Average Days
Below is the calculation in the field that I want to avg. in the report.
=DateDiff("d",[Date of Placement],[Date child(ren) returned home]) I'm still getting 0. I appreciate your time and help in this matter. "John Spencer" wrote: Pardon me, but the aggregate functions ignore nulls in the calculations. So I doubt that having null in a field will break the calculation of an average. Avg([Some Field]) should return a number or null if all the field values that are being aggregated are null. What type of field or value is [# of days in Custody]? Is it a field from a table or is it a calculation? If it is a calculated column in a query has it been formatted or does it use NZ in the query to ensure it returns a value? -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Klatuu" wrote in message ... The Null value is cause the problem. You can use the Nz function to prevent this. When you use the Nz function, you must put each element of the calculation that could possibly be null in a function, not the results for example, assume x = Null Nz(x + 10, 0) will return 0 Nz(x, 0) + 10 will return 10 =Avg(Nz([# of days in Custody],0)) BTW, # of days in Custody breaks almost every naming rule there is. Use only letters, numbers, and the underscore in names. Do not use spaces or any special character other than the underscore. Do not use any Access reserved words (Date, Value, Description, etc) Typical database naming stanards dictate all upper case with words separated with the underscore, so a good name would be: DAYS_IN_CUSTODY -- Dave Hargis, Microsoft Access MVP "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#8
|
|||
|
|||
Average Days
Hi again Nick
OK, now we know the sum is 0 then the avg will be 0 too. I would run a test query showing the 2 dates and the datediff calculation just so you get a feel for the results that are being averaged. Just to reinforce, as stated by John, the presence of a null won't cause the whole avg to be zero. Although, once you have solved the "zero problem", you will need to decide whether to convert the null datediffs to zeroes and that depends on what you want from the data. Firstly, why is the sum = 0? Scan the results of the test query. There are 2 possibilities... 1: The 2 dates on each row are on the same day so their difference (in days) is zero thus giving a sum and avg of zero. 2: You have positive datediffs and negative datediffs like... null to 12 May 2007 = null 10 May 2007 to 15 May 2007 = +5 12 May 2007 to 14 May 2007 = +2 18 May 2007 to 11 May 2007 = -7 Avg = (+5 +2 -7) / 3 = 0 / 3 = 0 If this is the case, presumably the negatives are wrong and the data itself needs to be corrected but, again, you know the data and the purpose so that is your call. Note: I also originally thought that maybe there aren't any rows where both dates are filled in. If this is true then all your datediffs will be null. The calculation would then be... Avg = Sum(all Nulls) / Count(all Nulls) Avg = Null / 0 Avg = Null But we know this isn't the case as you are getting Avg = 0 (not Null). Personal opinion re any null dates: I am seeing your 2 dates as a start and so if 1 or both are missing a duration can not be calculated. I wouldn't want to include these in my average so, because they are already being excluded, I would leave the current calculation as it is. But you know the data and the purpose so that's your call. Hope this helps Regards Andy Hull "Nick CWT" wrote: When I do Sum I get 0? When I do Count I get 2 = # of records with non-Null values. When I do =Avg(Nz([# of days in Custody],0)) I also get 0? I appreciate your time. Thanks. "Andy Hull" wrote: Hi Nick The nulls won't be included in the average so they won't cause the zero although you should decide whether to convert the nulls to zeroes themselves if you want them included in the average calculation. Eg Avg of null, 3 and 9 = 12 / 2 = 6 Avg of 0, 3 and 9 = 12 / 3 = 4 Is it possible that the Avg is very small and you are seeing it rounded? Try changing the format so you can see decimal places. Also, for testing only, add a Sum of Days in Custody and a Count of Days in Custody This should show you what numbers the Avg calculation is using. Hope this helps Andy Hull "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#9
|
|||
|
|||
Average Days
Try the following
=Avg(DateDiff("d",[Date of Placement],[Date child(ren) returned home])) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Nick CWT" wrote in message ... Below is the calculation in the field that I want to avg. in the report. =DateDiff("d",[Date of Placement],[Date child(ren) returned home]) I'm still getting 0. I appreciate your time and help in this matter. "John Spencer" wrote: Pardon me, but the aggregate functions ignore nulls in the calculations. So I doubt that having null in a field will break the calculation of an average. Avg([Some Field]) should return a number or null if all the field values that are being aggregated are null. What type of field or value is [# of days in Custody]? Is it a field from a table or is it a calculation? If it is a calculated column in a query has it been formatted or does it use NZ in the query to ensure it returns a value? -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Klatuu" wrote in message ... The Null value is cause the problem. You can use the Nz function to prevent this. When you use the Nz function, you must put each element of the calculation that could possibly be null in a function, not the results for example, assume x = Null Nz(x + 10, 0) will return 0 Nz(x, 0) + 10 will return 10 =Avg(Nz([# of days in Custody],0)) BTW, # of days in Custody breaks almost every naming rule there is. Use only letters, numbers, and the underscore in names. Do not use spaces or any special character other than the underscore. Do not use any Access reserved words (Date, Value, Description, etc) Typical database naming stanards dictate all upper case with words separated with the underscore, so a good name would be: DAYS_IN_CUSTODY -- Dave Hargis, Microsoft Access MVP "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
#10
|
|||
|
|||
Average Days
You da Man!!! That did it. Thanks John.
"John Spencer" wrote: Try the following =Avg(DateDiff("d",[Date of Placement],[Date child(ren) returned home])) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Nick CWT" wrote in message ... Below is the calculation in the field that I want to avg. in the report. =DateDiff("d",[Date of Placement],[Date child(ren) returned home]) I'm still getting 0. I appreciate your time and help in this matter. "John Spencer" wrote: Pardon me, but the aggregate functions ignore nulls in the calculations. So I doubt that having null in a field will break the calculation of an average. Avg([Some Field]) should return a number or null if all the field values that are being aggregated are null. What type of field or value is [# of days in Custody]? Is it a field from a table or is it a calculation? If it is a calculated column in a query has it been formatted or does it use NZ in the query to ensure it returns a value? -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Klatuu" wrote in message ... The Null value is cause the problem. You can use the Nz function to prevent this. When you use the Nz function, you must put each element of the calculation that could possibly be null in a function, not the results for example, assume x = Null Nz(x + 10, 0) will return 0 Nz(x, 0) + 10 will return 10 =Avg(Nz([# of days in Custody],0)) BTW, # of days in Custody breaks almost every naming rule there is. Use only letters, numbers, and the underscore in names. Do not use spaces or any special character other than the underscore. Do not use any Access reserved words (Date, Value, Description, etc) Typical database naming stanards dictate all upper case with words separated with the underscore, so a good name would be: DAYS_IN_CUSTODY -- Dave Hargis, Microsoft Access MVP "Nick CWT" wrote: I'm using the expression below to figure out the Average Number of days in custody from said field. I'm getting a 0 value in the report. Some of the data has Null values depending on the case; is this why I'm getting the 0? Need all the help I can get. =Avg([# of days in Custody]) Mr. Null & Void |
Thread Tools | |
Display Modes | |
|
|