QUERY 3/5 fields TRUE, then TRUE, otherwise FALSE.

[slagg7575]

Hi all,

I have a query with 5 fields, HIP, WAIST, HEIGHT, ARMS, LEGS. In each
of the feilds is a TRUE or FALSE text. What I need is to create a 6th
field called FINAL, and that feild either tallies, or counts that if 3
out of any 5 fields are TRUE, then TRUE for FINAL, if not then FALSE.
If one field is TRUE and the other 4 are FALSE, the FINAL field should
read FALSE. The critteria is any TRUE >3 FINAL is TRUE, otherwise
FALSE.

Hope this makes sense,

Thanks a bunch!

 

[John Vinson]

Hi all,

I have a query with 5 fields, HIP, WAIST, HEIGHT, ARMS, LEGS. In each
of the feilds is a TRUE or FALSE text. What I need is to create a 6th
field called FINAL, and that feild either tallies, or counts that if 3
out of any 5 fields are TRUE, then TRUE for FINAL, if not then FALSE.
If one field is TRUE and the other 4 are FALSE, the FINAL field should
read FALSE. The critteria is any TRUE >3 FINAL is TRUE, otherwise
FALSE.

Hope this makes sense,

Thanks a bunch!

Try:

Final: IIF(IIF([HIP] = "TRUE", 1, 0) + IIF([WAIST] = "TRUE", 1, 0) +
IIF([HEIGHT] = "TRUE", 1, 0) + IIF([ARMS] = "TRUE", 1, 0) + IIF([LEGS]
= "TRUE", 1, 0) >= 3, "TRUE", "FALSE")

If these fields are in fact of the Yes/No datatype rather than Text as
your message states, you can remove the = "TRUE" from all the IIF's,
and if you want Final to be a Yes/No field rather than a Text field,
remove the quotes around the final "TRUE" and "FALSE".

John W. Vinson[MVP]

 

[pietlinden]

SELECT tbDates.SomeDate, tbDates.Q1, tbDates.Q2, tbDates.Q3,
tbDates.Q4, tbDates.Q5, Abs([Q1]+[Q2]+[Q3]+[Q4]+[Q5]) AS TotalYes,
Abs([Q1]+[Q2]+[Q3]+[Q4]+[Q5])>=3 AS FinalAnswer
FROM tbDates;


you don't really need the TotalYes field. That was just intermediate.

 

[slagg7575]

Thanks for all the help,
The field is a text type, not a yes/no. What do I need to change in the
formula then?

Thanks!

 

[Douglas J. Steele]

SELECT tbDates.SomeDate, tbDates.Q1, tbDates.Q2, tbDates.Q3,
tbDates.Q4, tbDates.Q5,
Abs(([Q1] = 'True')+([Q2] = 'True' +([Q3] = 'True')+([Q4] = 'True')+([Q5] =
'True')) AS TotalYes,
Abs(([Q1] = 'True')+([Q2] = 'True' +([Q3] = 'True')+([Q4] = 'True')+([Q5] =
'True'))>=3 AS FinalAnswer
FROM tbDates;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for all the help,
The field is a text type, not a yes/no. What do I need to change in the
formula then?

Thanks!

SELECT tbDates.SomeDate, tbDates.Q1, tbDates.Q2, tbDates.Q3,
tbDates.Q4, tbDates.Q5, Abs([Q1]+[Q2]+[Q3]+[Q4]+[Q5]) AS TotalYes,
Abs([Q1]+[Q2]+[Q3]+[Q4]+[Q5])>=3 AS FinalAnswer
FROM tbDates;


you don't really need the TotalYes field. That was just intermediate.

 

[slagg7575]

Sorry guys, but that went WAY over my head. I am still a rookie at
this. I am not sure I understand. I just need if 3/5 fields are true to
have a new field true, otherwise false,and would it be possible to show
which feilds were true. Say HIP, WAIST, HEIGHT, that are the 3 TRUE
that gave me the TRUE result. I have changed the TRUE and FALSE text to
a 1, or 0., for later calculations. Can you guys help me. If i need to
clarify more I will! Thanks so much!

SELECT tbDates.SomeDate, tbDates.Q1, tbDates.Q2, tbDates.Q3,
tbDates.Q4, tbDates.Q5,
Abs(([Q1] = 'True')+([Q2] = 'True' +([Q3] = 'True')+([Q4] = 'True')+([Q5] =
'True')) AS TotalYes,
Abs(([Q1] = 'True')+([Q2] = 'True' +([Q3] = 'True')+([Q4] = 'True')+([Q5] =
'True'))>=3 AS FinalAnswer
FROM tbDates;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for all the help,
The field is a text type, not a yes/no. What do I need to change in the
formula then?

Thanks!

SELECT tbDates.SomeDate, tbDates.Q1, tbDates.Q2, tbDates.Q3,
tbDates.Q4, tbDates.Q5, Abs([Q1]+[Q2]+[Q3]+[Q4]+[Q5]) AS TotalYes,
Abs([Q1]+[Q2]+[Q3]+[Q4]+[Q5])>=3 AS FinalAnswer
FROM tbDates;


you don't really need the TotalYes field. That was just intermediate.