Hello there,
i am Trying to sum the total quality scores and then divide it by the sum of the total checks. At times their are 0 values in both the fields for a particular day. To fix that I trying to use the nz function however without any sucess. the Query runs but the output on a datasheet gives #Error.
My query is
SELECT FinalledAccounts.[Manager Name],Sum(Nz([Quality Score 1])) +Sum(Nz([Quality Score 2])) AS [Quality Score] ,Sum(Nz([Number Of Checks])) AS [Total Checks],Nz([Quality Score]/[Total Checks]) AS [Total Quality Score]
FROM FinalledAccounts
GROUP BY FinalledAccounts.[Manager Name], FinalledAccounts.WeekDate;

Kindly throw some light guys. Thanks

I have a hard time reading queries like that, I am much better looking at the design window, but you may want to try splitting this up into multiple queries. Then again, if you are experienced enough to understand the nz function, then you probably know to use multiple queries for complex operations. :)

Thankyou for your comments mate however what I am really looking for is a solution from a experienced access programmer.
Multiple queries is one way of doing that. However looking at that query Its a simple sum and group query which works and gives me the output correctly.there is nothing wrong in that query expect that when the quality score 1 and quality 2 score and the checks are 0, then 0 /0 will be a null value which gives an error in the datasheet view.Its seems i am not using the nz function correctly on that query. I have used nz on all three fields but when it comes to division of the variables Quality score and total checks where the output comes as #error.

Hopefully speaker 86 next time you post you will comeback with a solution.
Thanks for your time.

no need to be insulting ibmfreak. i doubt many people will help you if you continue with that attitude.

try searching for the NZ function in access help. you're doing it completely wrong.

or even do a search in these forums for NZ.... you could try putting in a little effort yourself, rather than belittling people who are offering suggestions (spearkers DID actually offer a couple of suggestions, but you may have been too biggoted to see that).

I am sorry if I have hurt anybody's feeling here.I did thank him for his comments.
Lets get back to the question. I have looked up and searched and I will get to the solution. Posting a question is one way of solving because more brains are involved. If you help me I learn and I help somebody else.
Could anybody give me a correct way of getting the Nz function into my query.
Everybody's Help is much appreciated. cheers

I did thank him for his comments.

actually, you didn't. what you did was edit your post later: you can see exactly when by the time and date stamp on the bottom of that post - you edited your post 3 minutes after i put my post in, then followed up 23 min later with a post to me trying to victimise yourself).

it's manipulative (aiming to victimise yourself by trying to make other forum users believe that i have been unreasonable towards you) and wrong (just because it is).

when i saw your (original) post it was a one-liner insult directed at speaker's perceived inexperience, and nothing more.

Posting a question is one way of solving because more brains are involved.

yes, another way is to do some research yourself - and believe me you can learn a hell of a lot more that way (i've done it myself).

despite your bad forum ettiquette, and general rudeness, i fail to see why i should help you further, yet here i am furthering your knowlegde anyway.

here are the two first hits google had on "access nz()" (that weren't related to new zealand):

http://office.microsoft.com/en-us/access/HA012288901033.aspx
http://msdn.microsoft.com/en-us/library/bb148937.aspx

i do sincerely hope it helps.

I have done by search on google which is quite logical. To be very frank I am smiling now.
The statement before about rudeness and ettiquette, I dont have to say anything about that as the people who will be reading this especially the adminstrator will know whose victimised whom. I think time is more precious then anything else so I will cut short and come to the solution.
One way of removing null values for the sum total is using a where condition for the total number of checks.
The query which worked for me was.

SELECT FinalledAccounts.[Manager Name], Sum([Quality Score 1])+Sum([Quality Score 2]) AS [Quality Score], Sum([Number Of Checks]) AS [Total Checks], ([Quality Score]/[Total Checks]) AS [Total Quality Score]
FROM FinalledAccounts
where [Number Of Checks] > 0
GROUP BY FinalledAccounts.[Manager Name], FinalledAccounts.WeekDate;]

This will remove null values from the data. I still want somebody to comeback with the Nz solution.

Ibmfreak.
Alone Man walks faster,
But with you by my side we can glide.

Emotions come and go Time doesnt. So value Time. Thankyou

actually, you didn't. what you did was edit your post later: you can see exactly when by the time and date stamp on the bottom of that post - you edited your post 3 minutes after i put my post in, then followed up 23 min later with a post to me trying to victimise yourself).

it's manipulative (aiming to victimise yourself by trying to make other forum users believe that i have been unreasonable towards you) and wrong (just because it is).

when i saw your (original) post it was a one-liner insult directed at speaker's perceived inexperience, and nothing more.



yes, another way is to do some research yourself - and believe me you can learn a hell of a lot more that way (i've done it myself).

despite your bad forum ettiquette, and general rudeness, i fail to see why i should help you further, yet here i am furthering your knowlegde anyway.

here are the two first hits google had on "access nz()" (that weren't related to new zealand):

http://office.microsoft.com/en-us/access/HA012288901033.aspx
http://msdn.microsoft.com/en-us/library/bb148937.aspx

i do sincerely hope it helps.


If that doesnt earn rep points, I dont know what does. Then again, maybe Im just too inexperienced to know.

actually - my apologies - the Nz() function will not help you an ounce in this instance (with the 0/0 error)

"0" (zero) is not a null value.
also, 0/0 is neither a value nor a null - it's treated as an error for the exact reason you described earlier: you cannot divide any number by "0".

consider an excel spreadsheet - the 'empty' cells are "nulls". the cells with "0" represent the value "0".

so the Nz() function will only help you if any of your fields that you are calculating are actually null (or 'empty').

what you need is the excel equivalent of the error check IsError() and also the Nz() IF any of your fields for the calculation are possibly null.... though i'm not sure about that. it MIGHT just work fine without the Nz() check.

i did a search in access help, and it turns out iserror() is a valid function in access as well as excel. put "iserror" in the access help search box. it will come up with something that will hopefully help you.

a tip: when i have used the iserror() function (admittedly only in excel) i have wrapped it in an if() function:

i.e.

if(iserror(A1/A2),"0",A1/A2)

which is to say: IF the calculation 'A1/A2' causes an error, return zero ("0") instead, otherwise do the calculation.

i'm not sure it=f it will work quite like that in access... try it and see... and post back with your progress.

I still want somebody to comeback with the Nz solution.
... sigh. you still really don't get it, do you.

I am sorry if I have hurt anybody's feeling here.I did thank him for his comments.
Lets get back to the question. I have looked up and searched and I will get to the solution. Posting a question is one way of solving because more brains are involved. If you help me I learn and I help somebody else.
Could anybody give me a correct way of getting the Nz function into my query.
Everybody's Help is much appreciated. cheers
If you read Wiklendt's posts you will see that your issue was to do with dividing by zero and not with Null values. This means that NZ is not applicable to your solution.

If you read Wildent's posts you will see that your issue was to do with dividing by zero and not with Null values. This means that NZ is not applicable to your solution.

Actually, Nulls could be part of the problem, just like the divide by zero could be part of the problem. I think it might be best to deal with BOTH just in case:

SELECT FinalledAccounts.[Manager Name], Sum(Nz([Quality Score 1],0))+Sum(Nz([Quality Score 2],0)) AS [Quality Score], Sum(Nz([Number Of Checks],0)) AS [Total Checks], IIf(Nz([Total Checks],0)=0,0,Nz([Quality Score],0)/[Total Checks] AS [Total Quality Score]
FROM FinalledAccounts
GROUP BY FinalledAccounts.[Manager Name], FinalledAccounts.WeekDate;]

this is the bit thats giving you the divide by zero

Nz([Quality Score]/[Total Checks]) AS [Total Quality Score]

so instead try this - you have to test whether its zero first, before you can safely divide by zero.
the nzs are useful in case any of the fields are blank, but may be superfluous

iif ( nz([total checks] ,0)= 0,0, nz([Quality Score],0)/[Total Checks])

this is the bit thats giving you the divide by zero



so instead try this

iif ( nz([total checks] ,0)= 0,0, nz([Quality Score],0)/[Total Checks])


Beat ya to it :D ;)

Actually, Nulls could be part of the problem, just like the divide by zero could be part of the problem. I think it might be best to deal with BOTH just in case:

SELECT FinalledAccounts.[Manager Name], Sum(Nz([Quality Score 1],0))+Sum(Nz([Quality Score 2],0)) AS [Quality Score], Sum(Nz([Number Of Checks],0)) AS [Total Checks], IIf(Nz([Total Checks],0)=0,0,Nz([Quality Score],0)/[Total Checks] AS [Total Quality Score]
FROM FinalledAccounts
GROUP BY FinalledAccounts.[Manager Name], FinalledAccounts.WeekDate;]

Bob, I must remember not to post after sharing a botte of wine with my wife.:D

Bob, I must remember not to post after sharing a botte of wine with my wife.:D

Who got the biggest portion? :)

Who got the biggest portion? :)That would be telling:D roughly 50- 50

I am going to try both the options given by bob and gemma. Thanks so very much for your comments. cheers.



:)

thanks for jumping in guys, i'd reached the extent of my expertise ;)