Full Version: average of zero
UtterAccess Forums > Microsoft® Access > Access Forms
DUG
So I got some much needed help here before, I had four values that I needed an average from and if any of the fields were left blank then I did not want that field included in the average. The help I got was great, except, when all four values are entered, the average comes up as zero.
IIf(IsNull([txtOD1])+IsNull([txtOD2])+IsNull([txtOD3])+IsNull([txtOD4]),(Nz([tx
tOD1],0)+Nz([txtOD2],0)+Nz([txtOD3],0)+Nz([txtOD4],0))/(4+IsNull([txtOD1])+IsNull([txtOD2])+IsNull([txtOD3])+IsNull([txtOD4])),0)
What needs to be changed?
theDBguy
Change the last part:
0)
to this:
,([txtOD1]+[txtOD2]+[txtOD3]+[txtOD4])/4)
HTH.
balaji
I am sorry to say, but if you have these as fields in your table, your table structure needs serious help. Your data structure is not properly normalized and will lead to lots of problems down the road. Can you imagine what kind of nasty code you have to write to calculate the standard deviation of these fields if it is needed down the road? You need to take these fields and make them records in a related table so that the database can manage the data better and you will be able to use that power to get the results you want much easier and quicker.
I have attached a set of links to this post that talk about normalization and other database design principles. I suggest you go through them and change your data structure as soon as you can.
DUG
Thanks DBguy, that piece worked perfect. Thanks Balaji, I know my DB is an patch-work mess, I have been limping along trying to avoid the full rebuild, your links are going to make it sooo much easier to finally do that. UtterAccess rox!
theDBguy
You're very welcome. Good luck.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.