434,660 Members | 1,953 Online
Need help? Post your question and get tips & solutions from a community of 434,660 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

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.

14 Replies

 Expert Mod 15k+ P: 31,487 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 =IIf([Field1]='Yes',1,0)+  IIf([Field2]='Yes',1,0)+  ... 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 = -( ([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

 Expert Mod 15k+ P: 31,487 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

 Expert Mod 15k+ P: 31,487 @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 Person ID, Question No., YesNoResponse, TextualResponse 1          1             Y 1          2             N 1          3             Y 2          1             N 2          2             N 2          3             Y   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

 Expert Mod 15k+ P: 31,487 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

 Expert Mod 15k+ P: 31,487 FEHRENHITY: Sorry for confusing you Not a problem. All clear now anyway. FEHRENHITY: I’m 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