By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 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
  1. 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
Share this Question
Share on Google+
12 Replies


nico5038
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

Scott Price
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

NeoPa
Expert Mod 15k+
P: 31,186
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
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. 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

Scott Price
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

Scott Price
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
  1. SELECT tblAdmin.EmployeeID, Count(tblAdmin.Performance_1) AS CountOfPerformance_1
  2. FROM tblAdmin
  3. GROUP BY tblAdmin.EmployeeID, tblAdmin.Performance_1
  4. HAVING (((tblAdmin.EmployeeID)=1) AND ((tblAdmin.Performance_1)="5"));
  5.  
Regards,
Scott
Oct 15 '07 #8

nico5038
Expert 2.5K+
P: 3,072
Hmm, as stated before, your table should have been designed to hold one row per question like:
EmployeeA, Q1, Answer1
EmployeeA, Q2, Answer2
EmployeeA, Q3, Answer3
...etc...
EmployeeB, Q1, Answer1
...etc...

Having such a table will allow a GroupBy query counting the number of "5" values per user easily.

We can "fake" such a table by using a UNION query that's generating such a table and then we can use the UNION for the needed GroupBy.

The UNION will look like:
Expand|Select|Wrap|Line Numbers
  1. Select EmployeeID, "performance_1" as Question, [performance_1] as Answer from tblAdmin
  2. UNION
  3. Select EmployeeID, "performance_2" as Question, [performance_2] as Answer from tblAdmin
  4. UNION
  5. Select EmployeeID, "performance_3" as Question, [performance_3] as Answer from tblAdmin
  6. UNION
  7. ... etc ...
  8.  
Getting the idea ?

Nic;o)
Oct 15 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
Elaine,

How many performance_ fields are there in the record?
Oct 16 '07 #10

P: 51
Thanks for everyone's help.
There are about 25 questions per survey. I've tried Scott's way, it works. But since I have to use one column per performance question per category, and there are 6 categories re: 5, 4, 3, 2, 1 and 0; I am afraid that I might run out of of columns, but I'll try.
Thanks!

Elaine
Oct 23 '07 #11

nico5038
Expert 2.5K+
P: 3,072
Hmm, would still want to ask you to try the UNION I proposed and after defining it for e.g. 5 questions use it in a crosstable query.
It will allow then one line for each question and for each category a column.
Also the pivot will be possible showing the six categories and the questions as columns.

Nic;o)
Oct 23 '07 #12

P: 51
Thanks Nico for your help, I was able to complete the querry. It's working now.
Thanks!
Elaine
Oct 23 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.