By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Count specific text from fields in one row

P: 6
Hi
If I have the below scenario
1- Table with 10 columns
2- First 8 columns with Data Type TEXT as Combo Box with Row Source "NA";"No";"Yes"

Q1 - In filed number 9, how I can count how many Yes within the same row from the first 8 fields
Q2 In filed number 10, how I can count how many No within the same row from the first 8 fields

Please help, thanks a lot
Apr 11 '12 #1

✓ answered by Mihail

The same solution as NeoPa's one but a little bit shorter:
Expand|Select|Wrap|Line Numbers
  1. = -( ([Field1]='Yes') + ([Field2]='Yes') + ......)
But I do not post for that.
I wish to ask you why you need to store this results ?
Doing that your database will become, suddenly, not normalized.

Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,186
There is no easy, or built-in, way of doing this. It may come down to something as clumsy as :

Expand|Select|Wrap|Line Numbers
  1. =IIf([Field1]='Yes',1,0)+
  2.  IIf([Field2]='Yes',1,0)+
  3.  ...
Apr 11 '12 #2

P: 6
Thaaaaaaaaaaaaaanks a lot
Much appreciate
Apr 12 '12 #3

100+
P: 759
The same solution as NeoPa's one but a little bit shorter:
Expand|Select|Wrap|Line Numbers
  1. = -( ([Field1]='Yes') + ([Field2]='Yes') + ......)
But I do not post for that.
I wish to ask you why you need to store this results ?
Doing that your database will become, suddenly, not normalized.
Apr 12 '12 #4

P: 6
Thanks a lot Mihail for your support
Actually, I create a small database for survey purpose and each row has reference number with the question’s answers
So I was tried to get how many Yes and No on each survey
This project is not serious, but I’m trying to learn from this projects

Thanks a gain
Apr 12 '12 #5

100+
P: 759
I understand.
Well, first thing to learn is to not store in a database something what you can calculate using data from that database.
I think that the best point to start learning is here: Database Normalization and Table Structures.
Apr 12 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
Mihail:
The same solution as NeoPa's one but a little bit shorter:
Indeed. How I'd probably do it myself were I in that position, but a little harder to explain clearly (hence the slightly longer, but easier to understand suggestion). That would certainly be a reliable solution though.
Apr 15 '12 #7

P: 6
Hi NeoPa
How are u?:)
Really I appreciate your solution, because it makes sense and it makes me understand how to create equation like this

And many Thanks for Mihail, he used the same way but little shorter, I site a little to understand how is the equation it work.

Both of you really was helpful and it’s very nice to find friends like you to support and learn from their experience,

Many Thanks
Ahmad
Apr 15 '12 #8

P: 62
In my opinion, you might be better off storing the survey data like that:
Person ID, Question No., YesNoResponse, TextualResponse

It would allow counting using an aggregate function.
Apr 15 '12 #9

NeoPa
Expert Mod 15k+
P: 31,186
@Ahmad.
Thank you. A pleasure to help.

I also recommend limweizhong's post for better general advice. The answer to the question is as you've selected, but following that advice means you don't even need to ask it in the first place ;-)
Apr 17 '12 #10

P: 6
Thanks all for helping and supporting me,
@NeoPa: you know I didn't mean that, really I appreciate your support and I used your solution with different case :)
@limweizhong: I already create the survey with reference number, 70 fields with Yes/No/NA, field with auto capture for User ID, field with auto capture for Time Date Stamp & some fields for calculation
I create all the require tables / Forms. Still reports from filtered form, Split the database with logins control and this is my first time to do that

Thanks again for everyone
Apr 18 '12 #11

P: 62
No what I meant is that instead of storing your yes/no responses for different questions in separate columns you store it in separate rows, so each row becomes exactly one response, for a user, for a question. Like:
Expand|Select|Wrap|Line Numbers
  1. Person ID, Question No., YesNoResponse, TextualResponse
  2. 1          1             Y
  3. 1          2             N
  4. 1          3             Y
  5. 2          1             N
  6. 2          2             N
  7. 2          3             Y
  8.  
Assuming there are three questions in the survey, and two people have answered them.

Of course, if your data comes from a source where you cannot change the format, then you might need to do a bit more work. Probably an Excel worksheet with creative formulas or macros would do the trick.
Apr 18 '12 #12

NeoPa
Expert Mod 15k+
P: 31,186
FEHRENHITY:
@NeoPa: you know I didn't mean that, really I appreciate your support and I used your solution with different case :)
I'm not sure what you thought I meant, but I was perfectly happy with all your responses. My comment was as an expert and not a moderator when I advised you consider limweizhong's advice. I also meant it when I suggested that the post you selected as Best Answer was the correct one to select. That may seem strange, but the best advice is not necessarily the most direct answer to a question.

I have little idea what you thought I was saying, but I assure you it was not criticism of any kind.
Apr 18 '12 #13

P: 6
Hi NeoPa

Hhhhhhhhhhhhh
My English language is not good, and I’m trying to improve it
I don’t mean anything just I’m trying to appreciate your support
Sorry for confusing you

Please forget that
Apr 19 '12 #14

NeoPa
Expert Mod 15k+
P: 31,186
FEHRENHITY:
Sorry for confusing you
Not a problem. All clear now anyway.

FEHRENHITY:
Im trying to learn creating reports from filtered form, Do you have any link for that
You need to ask this in a separate thread. Only one question allowed per thread here.

NB. I will edit your post to remove the new question.
Apr 19 '12 #15

Post your reply

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