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
|
|||
|
|||
Dropping Zeros from Avg
I have 6 values (S1 - S6). Some of these fields have values, and some
contain 0 (zero). I need to average these values... NOT including any zero values. In my query behind the form, I place 6 fields like this... S1Count : IIF(S1=0,0,1) S2Count : IIF(S2=0,0,1)... etc for all 6 Now I can get my average with... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count Works fine, but... isn't there an easier way? Thanks in advance, Al Camp --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 |
#2
|
|||
|
|||
One way would be to consider re-defining your data (table) structure. Your
description matches what you'd do if you were using a spreadsheet, but the relational database approach would be to use one field for the value, and a second field to "define" it (you've used one field each to define). Access has some very good functions and tools, but they don't work if you don't organize your data relationally. -- Good luck Jeff Boyce Access MVP "AlCamp" wrote in message ... I have 6 values (S1 - S6). Some of these fields have values, and some contain 0 (zero). I need to average these values... NOT including any zero values. In my query behind the form, I place 6 fields like this... S1Count : IIF(S1=0,0,1) S2Count : IIF(S2=0,0,1)... etc for all 6 Now I can get my average with... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count Works fine, but... isn't there an easier way? Thanks in advance, Al Camp --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 |
#3
|
|||
|
|||
Just add all the values and divide by the number that are not zero.
The expression (S10) should return 1 (true) if the value if greater than zero and 0 (false) otherwise. An average normally includes all values not just those that are not zero. "AlCamp" wrote: I have 6 values (S1 - S6). Some of these fields have values, and some contain 0 (zero). I need to average these values... NOT including any zero values. In my query behind the form, I place 6 fields like this... S1Count : IIF(S1=0,0,1) S2Count : IIF(S2=0,0,1)... etc for all 6 Now I can get my average with... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count Works fine, but... isn't there an easier way? Thanks in advance, Al Camp --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 |
#4
|
|||
|
|||
Jeff,
Couldn't disagree more... These are 6 distinct , unrelated, and disparate values within one record, NOT a "real" one to many relationship. There will never be more than these six separate category values, and there are no other calculations against these values. The client and I decided that for just 6 values we wouldn't add another table ( with [SValue] and [SNumber] fields as you suggest) and another subform (that would require 2 entered values in each record... rather than just one). I'm well aware of the rules of Normalization, but I'm not going to be a slave to them. but they don't work if you don't organize your data relationally. Well, I'm sorry that my table design doesn't meet your high standards! Since you weren't able to help me with my averaging question... if I figure out a better method, I'll be sure to pass it along. Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... One way would be to consider re-defining your data (table) structure. Your description matches what you'd do if you were using a spreadsheet, but the relational database approach would be to use one field for the value, and a second field to "define" it (you've used one field each to define). Access has some very good functions and tools, but they don't work if you don't organize your data relationally. -- Good luck Jeff Boyce Access MVP "AlCamp" wrote in message ... I have 6 values (S1 - S6). Some of these fields have values, and some contain 0 (zero). I need to average these values... NOT including any zero values. In my query behind the form, I place 6 fields like this... S1Count : IIF(S1=0,0,1) S2Count : IIF(S2=0,0,1)... etc for all 6 Now I can get my average with... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count Works fine, but... isn't there an easier way? Thanks in advance, Al Camp --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 |
#5
|
|||
|
|||
Al
I tried to interpret from your description what you might be dealing with. I wasn't intending to disrespect your design or your knowledge. And if you knew me better, the "high standards" comment wouldn't have been applicable. For example, if you are trying to take the average of 6 values, this, to me, implies that there is something in common about those values. Otherwise, I have trouble imagining what an "average" would mean. This is what I was suggesting. I, too, try not to be a slave to normalization, and while there are "rules", I believe each situation is different. Perhaps if you provide a bit more information about your situation, the 'group can offer a better suggestion... Good luck! Jeff Boyce Access MVP "AlCamp" wrote in message ... Jeff, Couldn't disagree more... These are 6 distinct , unrelated, and disparate values within one record, NOT a "real" one to many relationship. There will never be more than these six separate category values, and there are no other calculations against these values. The client and I decided that for just 6 values we wouldn't add another table ( with [SValue] and [SNumber] fields as you suggest) and another subform (that would require 2 entered values in each record... rather than just one). I'm well aware of the rules of Normalization, but I'm not going to be a slave to them. but they don't work if you don't organize your data relationally. Well, I'm sorry that my table design doesn't meet your high standards! Since you weren't able to help me with my averaging question... if I figure out a better method, I'll be sure to pass it along. Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... One way would be to consider re-defining your data (table) structure. Your description matches what you'd do if you were using a spreadsheet, but the relational database approach would be to use one field for the value, and a second field to "define" it (you've used one field each to define). Access has some very good functions and tools, but they don't work if you don't organize your data relationally. -- Good luck Jeff Boyce Access MVP "AlCamp" wrote in message ... I have 6 values (S1 - S6). Some of these fields have values, and some contain 0 (zero). I need to average these values... NOT including any zero values. In my query behind the form, I place 6 fields like this... S1Count : IIF(S1=0,0,1) S2Count : IIF(S2=0,0,1)... etc for all 6 Now I can get my average with... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count Works fine, but... isn't there an easier way? Thanks in advance, Al Camp --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 |
#6
|
|||
|
|||
Thanks Jeff,
I guess I did overreact a bit. I apologize. Sometimes we read a "tone" into someone's response that isn't really there. It's one of the shortcomings of email "conversations." As I said, I do understand what you are suggesting. You would create a related table with 2 fields [SValue] (a numerical value to be averaged) and [SType] (ex. entries "S1" thru "S6"). Then, of course, this whole "non-zero" averaging would be much simpler. I think this type of "flat" relationship is more appropriate where there are only 2 or 3 values, and that 6 values is pushing the "non-normalization" a bit, but we really didn't want to add a table and subform for just this simple calculation. Also, because I didn't have a subform, I was able to design a more user friendly "one line" continuous form (with S1-S6 values displayed horizontally) for user entry. I realize that my design is a bit odd... so I shouldn't be surprised when someone says, "Hey... that's odd." Thanks, Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Al I tried to interpret from your description what you might be dealing with. I wasn't intending to disrespect your design or your knowledge. And if you knew me better, the "high standards" comment wouldn't have been applicable. For example, if you are trying to take the average of 6 values, this, to me, implies that there is something in common about those values. Otherwise, I have trouble imagining what an "average" would mean. This is what I was suggesting. I, too, try not to be a slave to normalization, and while there are "rules", I believe each situation is different. Perhaps if you provide a bit more information about your situation, the 'group can offer a better suggestion... Good luck! Jeff Boyce Access MVP "AlCamp" wrote in message ... Jeff, Couldn't disagree more... These are 6 distinct , unrelated, and disparate values within one record, NOT a "real" one to many relationship. There will never be more than these six separate category values, and there are no other calculations against these values. The client and I decided that for just 6 values we wouldn't add another table ( with [SValue] and [SNumber] fields as you suggest) and another subform (that would require 2 entered values in each record... rather than just one). I'm well aware of the rules of Normalization, but I'm not going to be a slave to them. but they don't work if you don't organize your data relationally. Well, I'm sorry that my table design doesn't meet your high standards! Since you weren't able to help me with my averaging question... if I figure out a better method, I'll be sure to pass it along. Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... One way would be to consider re-defining your data (table) structure. Your description matches what you'd do if you were using a spreadsheet, but the relational database approach would be to use one field for the value, and a second field to "define" it (you've used one field each to define). Access has some very good functions and tools, but they don't work if you don't organize your data relationally. -- Good luck Jeff Boyce Access MVP "AlCamp" wrote in message ... I have 6 values (S1 - S6). Some of these fields have values, and some contain 0 (zero). I need to average these values... NOT including any zero values. In my query behind the form, I place 6 fields like this... S1Count : IIF(S1=0,0,1) S2Count : IIF(S2=0,0,1)... etc for all 6 Now I can get my average with... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count Works fine, but... isn't there an easier way? Thanks in advance, Al Camp --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 |
#7
|
|||
|
|||
Al
From your responses, you already are aware that the "flat" design will cause you problems if there's ever a change in how many "S"s you have. If you are confident that you will always have six (or fewer), you could consider setting a reference to the Excel function that does averages, and building a function in Access that calls that Excel function, passing in the "S1", "S2", ... values. Good luck Jeff "AlCamp" wrote in message ... Thanks Jeff, I guess I did overreact a bit. I apologize. Sometimes we read a "tone" into someone's response that isn't really there. It's one of the shortcomings of email "conversations." As I said, I do understand what you are suggesting. You would create a related table with 2 fields [SValue] (a numerical value to be averaged) and [SType] (ex. entries "S1" thru "S6"). Then, of course, this whole "non-zero" averaging would be much simpler. I think this type of "flat" relationship is more appropriate where there are only 2 or 3 values, and that 6 values is pushing the "non-normalization" a bit, but we really didn't want to add a table and subform for just this simple calculation. Also, because I didn't have a subform, I was able to design a more user friendly "one line" continuous form (with S1-S6 values displayed horizontally) for user entry. I realize that my design is a bit odd... so I shouldn't be surprised when someone says, "Hey... that's odd." Thanks, Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Al I tried to interpret from your description what you might be dealing with. I wasn't intending to disrespect your design or your knowledge. And if you knew me better, the "high standards" comment wouldn't have been applicable. For example, if you are trying to take the average of 6 values, this, to me, implies that there is something in common about those values. Otherwise, I have trouble imagining what an "average" would mean. This is what I was suggesting. I, too, try not to be a slave to normalization, and while there are "rules", I believe each situation is different. Perhaps if you provide a bit more information about your situation, the 'group can offer a better suggestion... Good luck! Jeff Boyce Access MVP "AlCamp" wrote in message ... Jeff, Couldn't disagree more... These are 6 distinct , unrelated, and disparate values within one record, NOT a "real" one to many relationship. There will never be more than these six separate category values, and there are no other calculations against these values. The client and I decided that for just 6 values we wouldn't add another table ( with [SValue] and [SNumber] fields as you suggest) and another subform (that would require 2 entered values in each record... rather than just one). I'm well aware of the rules of Normalization, but I'm not going to be a slave to them. but they don't work if you don't organize your data relationally. Well, I'm sorry that my table design doesn't meet your high standards! Since you weren't able to help me with my averaging question... if I figure out a better method, I'll be sure to pass it along. Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... One way would be to consider re-defining your data (table) structure. Your description matches what you'd do if you were using a spreadsheet, but the relational database approach would be to use one field for the value, and a second field to "define" it (you've used one field each to define). Access has some very good functions and tools, but they don't work if you don't organize your data relationally. -- Good luck Jeff Boyce Access MVP "AlCamp" wrote in message ... I have 6 values (S1 - S6). Some of these fields have values, and some contain 0 (zero). I need to average these values... NOT including any zero values. In my query behind the form, I place 6 fields like this... S1Count : IIF(S1=0,0,1) S2Count : IIF(S2=0,0,1)... etc for all 6 Now I can get my average with... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count Works fine, but... isn't there an easier way? Thanks in advance, Al Camp --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 |
#8
|
|||
|
|||
Jeff,
My client did agree to this structure, and are aware that if they renege later, they will "pay the piper." setting a reference to the Excel function that does averages... That's a good suggestion. I've never tried calling to an Excel function to handle a calculation I need in Access. I'll check that out. To be honest though, my... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count works just fine, and the client likes it, so we'll probably stick with that. No use reinventing the wheel... Thanks for all your help. Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Al From your responses, you already are aware that the "flat" design will cause you problems if there's ever a change in how many "S"s you have. If you are confident that you will always have six (or fewer), you could consider setting a reference to the Excel function that does averages, and building a function in Access that calls that Excel function, passing in the "S1", "S2", ... values. Good luck Jeff "AlCamp" wrote in message ... Thanks Jeff, I guess I did overreact a bit. I apologize. Sometimes we read a "tone" into someone's response that isn't really there. It's one of the shortcomings of email "conversations." As I said, I do understand what you are suggesting. You would create a related table with 2 fields [SValue] (a numerical value to be averaged) and [SType] (ex. entries "S1" thru "S6"). Then, of course, this whole "non-zero" averaging would be much simpler. I think this type of "flat" relationship is more appropriate where there are only 2 or 3 values, and that 6 values is pushing the "non-normalization" a bit, but we really didn't want to add a table and subform for just this simple calculation. Also, because I didn't have a subform, I was able to design a more user friendly "one line" continuous form (with S1-S6 values displayed horizontally) for user entry. I realize that my design is a bit odd... so I shouldn't be surprised when someone says, "Hey... that's odd." Thanks, Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Al I tried to interpret from your description what you might be dealing with. I wasn't intending to disrespect your design or your knowledge. And if you knew me better, the "high standards" comment wouldn't have been applicable. For example, if you are trying to take the average of 6 values, this, to me, implies that there is something in common about those values. Otherwise, I have trouble imagining what an "average" would mean. This is what I was suggesting. I, too, try not to be a slave to normalization, and while there are "rules", I believe each situation is different. Perhaps if you provide a bit more information about your situation, the 'group can offer a better suggestion... Good luck! Jeff Boyce Access MVP "AlCamp" wrote in message ... Jeff, Couldn't disagree more... These are 6 distinct , unrelated, and disparate values within one record, NOT a "real" one to many relationship. There will never be more than these six separate category values, and there are no other calculations against these values. The client and I decided that for just 6 values we wouldn't add another table ( with [SValue] and [SNumber] fields as you suggest) and another subform (that would require 2 entered values in each record... rather than just one). I'm well aware of the rules of Normalization, but I'm not going to be a slave to them. but they don't work if you don't organize your data relationally. Well, I'm sorry that my table design doesn't meet your high standards! Since you weren't able to help me with my averaging question... if I figure out a better method, I'll be sure to pass it along. Al Camp "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... One way would be to consider re-defining your data (table) structure. Your description matches what you'd do if you were using a spreadsheet, but the relational database approach would be to use one field for the value, and a second field to "define" it (you've used one field each to define). Access has some very good functions and tools, but they don't work if you don't organize your data relationally. -- Good luck Jeff Boyce Access MVP "AlCamp" wrote in message ... I have 6 values (S1 - S6). Some of these fields have values, and some contain 0 (zero). I need to average these values... NOT including any zero values. In my query behind the form, I place 6 fields like this... S1Count : IIF(S1=0,0,1) S2Count : IIF(S2=0,0,1)... etc for all 6 Now I can get my average with... S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count Works fine, but... isn't there an easier way? Thanks in advance, Al Camp --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.791 / Virus Database: 535 - Release Date: 11/8/2004 |
#9
|
|||
|
|||
Al
Your expression: S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count doesn't appear handle a Null. The only "enhancement" you may wish to consider would use the Nz() function as needed. Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Leading zeros are shown in Access? | KayM | New Users | 2 | September 9th, 2004 04:31 PM |
Leading zeros in CSV and Excel 97 | Leslie | General Discussion | 1 | August 13th, 2004 12:03 AM |
Linked Table field...strip off leading zeros of text | Inyo55 | Database Design | 1 | July 24th, 2004 01:11 AM |
Excluding zeros | Ben | Charts and Charting | 1 | December 11th, 2003 03:50 PM |
Leading Zero's | Tami | Worksheet Functions | 2 | November 24th, 2003 03:27 PM |