435,081 Members | 1,885 Online
Need help? Post your question and get tips & solutions from a community of 435,081 IT Pros & Developers. It's quick & easy.

# Using OR and IIF

 P: 51 Hi, I am doing a querry to calculate the customer satisfacation in my division and I need to know how many 5(always exceed) the employee received in one evaluation from a customer for all the questions. And there are 20 quetsions. Here is my code that I wrote in the querry, but the count is not right, it gave me the total of 6, but the total suppose to be 20 because I have 5 of 5in performance_1, 5 of 5 in performance_2, 5 of 5 in performance_3, and 5 of 5 in performance_4. Count 5: Expand|Select|Wrap|Line Numbers Count(IIf([performance_1]=5 Or [Performance_2]=5 Or [Performance_3]=5 Or [Performance_4]=5 Or [Performance_5]=5 Or [Performance_6]=5 Or [Performance_7]=5 Or [Performance_8]=5 Or [Performance_9]=5 Or [Performance_10]=5 Or [Performance_11]=5 Or [Performance_12] Or [Performance_13]=5 Or [Performance_14]=5 Or [Professionalism]=5 Or [Confidentiality]=5 Or [Ownership]=5 Or [Accountability]=5 Or [Commitment]=5 Or [Communication]=5 Or [Telephone]=5 Or [Stewardship]=5 Or [Safety]=5 Or [Caring]=5 Or [Overall]=5,"yes")) can somebody help please. thanks a lot and have a nice day. Elaine Oct 12 '07 #1
12 Replies

 Expert 2.5K+ P: 3,072 Hi, I am doing a querry to calculate the customer satisfacation in my division and I need to know how many 5(always exceed) the employee received in one evaluation from a customer for all the questions. And there are 20 quetsions. Here is my code that I wrote in the querry, but the count is not right, it gave me the total of 6, but the total suppose to be 20 because I have 5 of 5in performance_1, 5 of 5 in performance_2, 5 of 5 in performance_3, and 5 of 5 in performance_4. Count 5: Count(IIf([performance_1]=5 Or [Performance_2]=5 Or [Performance_3]=5 Or [Performance_4]=5 Or [Performance_5]=5 Or [Performance_6]=5 Or [Performance_7]=5 Or [Performance_8]=5 Or [Performance_9]=5 Or [Performance_10]=5 Or [Performance_11]=5 Or [Performance_12] Or [Performance_13]=5 Or [Performance_14]=5 Or [Professionalism]=5 Or [Confidentiality]=5 Or [Ownership]=5 Or [Accountability]=5 Or [Commitment]=5 Or [Communication]=5 Or [Telephone]=5 Or [Stewardship]=5 Or [Safety]=5 Or [Caring]=5 Or [Overall]=5,"yes")) can somebody help please. thanks a lot and have a nice day. Elaine Hi Elaine, Looks like your table isn't really "normalized", but this problem I guess is Nulls related. When there's one of the fields holding a Null (nothing/unknown) value, the comparison won't work. The solution will be to use the NZ() function like: Count 5: Count(IIf(NZ([performance_1])=5 Or NZ([Performance_2])=5 Or NZ([Performance_3])=5 .... etc.. Getting the idea ? Nic;o) Oct 12 '07 #2

 Expert 100+ P: 1,384 In addition to what Nico suggests, I think you are also expecting something different from the Count() function than what it does! Count() only 'counts' the occurrences of a certain field according to your criteria. You sound like you're trying to accomplish more of a Sum() function. Regards, Scott Oct 12 '07 #3

 Expert Mod 15k+ P: 31,494 Elaine, As Scott says, I think we may need a bit of a rethink here. Can you post your record layout for us (Table Meta-Data) and describe how it works (what it means). That way we can suggest a more appropriate way to process the data. Here is an example of how to post table MetaData : Table Name=tblStudent Expand|Select|Wrap|Line Numbers Field; Type; IndexInfo StudentID; AutoNumber; PK Family; String; FK Name; String University; String; FK Mark; Numeric LastAttendance; Date/Time Oct 12 '07 #4

 P: 51 HI, Here is my table data structure: tblAdmin (for table Administration) fields: performance_1, type is number, byte performance_2, type is number, byte, performance_3, type is number, byte, etc. For all these fields, the criteria is 5 is Always Exceeds 4 is Often Exceeds 3 is Consistently meets 2 is Sometimes meets 1 is Does not Meet 0 is N/A I want to have a querry that count how many 5 "this person" has for all the customer evaluation questions re: performance_1, performance_, etc. Thanks! Oct 15 '07 #5

 Expert 100+ P: 1,384 Hi Elaine, A little further clarification, please. In your first post you sound as if you want to add the 5's together, to result in 20 (four 5's), while in your last post you sound as if you want to count how many 5's there are 4 (four 5's). Which is it? Count() returns how many 5's there are. Sum() adds the 5's together. Regards, Scott Oct 15 '07 #6

 P: 51 Scott, Sorry for the confusion, I want to count how many 5's this employee received for his/her evaluation, not the sum. for example. There are 20 questions in an evaluation form, this person completed 2 surveys for Jan, therefore, the total of question is 40. I want to coutn how many 5 she received. For example if she received 20. the rate for her "Always exceeds" will be 20/40. Thanks! Oct 15 '07 #7

 Expert 100+ P: 1,384 Elaine, Sorry to be the bearer of bad news, but I'm not sure that you can do it the way you are trying. An alternative approach (although quite grotesque) is to use a number of queries, pulling the count of each category, which you will then sum in another query. Here's an example of the first query: Expand|Select|Wrap|Line Numbers SELECT tblAdmin.EmployeeID, Count(tblAdmin.Performance_1) AS CountOfPerformance_1 FROM tblAdmin GROUP BY tblAdmin.EmployeeID, tblAdmin.Performance_1 HAVING (((tblAdmin.EmployeeID)=1) AND ((tblAdmin.Performance_1)="5"));   Regards, Scott Oct 15 '07 #8