Count specific text from fields in one row

[FEHRENHITY]

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

[NeoPa]

There is no easy, or built-in, way of doing this. It may come down to something as clumsy as :

=IIf([Field1]='Yes',1,0)+
 IIf([Field2]='Yes',1,0)+
 ...

[FEHRENHITY]

Thaaaaaaaaaaaaa anks a lot
Much appreciate

[Mihail]

The same solution as NeoPa's one but a little bit shorter:

= -( ([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.

[FEHRENHITY]

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

[Mihail]

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.

[NeoPa]

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.

[FEHRENHITY]

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

[limweizhong]

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.

[NeoPa]

@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 ;-)

[FEHRENHITY]

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

[limweizhong]

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:

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.

[NeoPa]

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.

[FEHRENHITY]

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

[NeoPa]

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.