Or Feature

[simon4amiee]

Hi again I have a database that record answers from 20 questions (Yes, No, N/A), and I need to run a query to show me ALL records that return a NO answer, I tried putting "NO" in Q1 then going down a level to OR 'NO' in the Q2 and so on but i ran out of spaces, any ideas to make it simple!

 

[DanWall]

You should just need to put "NO" in the first criteria row.

The rows are OR criteria.

For example if you only want to show YES and NO, then you would put YES in the criteria and NO in the first OR row.
That translates to: Show records where Answers = YES or NO

 

[simon4amiee]

Sorry i have 20 fields for 20 questions each is yes or no, and i need to show the data if just 1 question turns up a NO, this applies to all 20 fields

 

[DanWall]

Hmmm, I'm not sure.... My apologies, I'm merely a learner...

 

[Brianwarnock]

Assuming that there is no limit switch to SQL view and carry on coding.

Brian

 

[Rabbie]

It sounds as if your data is not correctly normalized. It would be much easier for you if you had a separate record for each answer.

 

[Atomic Shrimp]

I agree with Rabbie - if you want to extend it to 40, 60, 150 questions, you won't be able to keep on adding fields for the answers.

For your existing question though (and assuming these are text fields - based on the fact that they can contain 'n/a' as well as yes or no), I would handle it a bit differently - this method is a bit quick and dirty, but it should work:

Instead of checking each field for the value, concatenate all the fields together and check the resulting mess for the presence of 'No', something like:

SELECT tblAnswers.*
FROM tblAnswers
WHERE (([Q1] & [Q2] & [Q3] & [Q4] & [Q5] & [Q6] & [Q7] & [Q8] & [Q9] & [Q10] & [Q11] & [Q12] & [Q13] & [Q14] & [Q15] & [Q16] & [Q17] & [Q18] & [Q19] & [Q20]) Like "*No*");

What this basically does, is to create - on the fly, for each record - a big string that probably looks something like "YesYesYesYesNoYesYesYesNoYesYesN/aYesYesYesYesYesYesNoN/a", then checks if the string 'No' appears in it anywhere

This approach could go wrong if some chance confluence of different values happened to match the string your're looking for - i.e. if the possible answers included (silly example) 'Aye' and 'Sure' as well as Yes, No, etc, Because 'AyeSure' contains the string 'yeS' (granted it probably wouldn't make a difference in this silly example, as they are all affirmative)

 

[simon4amiee]

It sounds as if your data is not correctly normalized. It would be much easier for you if you had a separate record for each answer.

I dont understand sorry bud, separate record??, for each person there is a subdatasheet, which has a separate feild for every question, they have a drop down option of Yes / No / N/A, like i mentioned the only difficulty is running a query on the entire database where a No is found in one of those fields, its a hand hygeine Assessment if the fail one question they fail the whole assessment, so basically i need a report to show just the people that failed. Hope ive explained a little better.

Could i create a new field which is like a calculation of all the questions where is returnes a Y or N, depending on the results.

 

[simon4amiee]

I agree with Rabbie - if you want to extend it to 40, 60, 150 questions, you won't be able to keep on adding fields for the answers.

For your existing question though (and assuming these are text fields - based on the fact that they can contain 'n/a' as well as yes or no), I would handle it a bit differently - this method is a bit quick and dirty, but it should work:

Instead of checking each field for the value, concatenate all the fields together and check the resulting mess for the presence of 'No', something like:

SELECT tblAnswers.*
FROM tblAnswers
WHERE (([Q1] & [Q2] & [Q3] & [Q4] & [Q5] & [Q6] & [Q7] & [Q8] & [Q9] & [Q10] & [Q11] & [Q12] & [Q13] & [Q14] & [Q15] & [Q16] & [Q17] & [Q18] & [Q19] & [Q20]) Like "*No*");

What this basically does, is to create - on the fly, for each record - a big string that probably looks something like "YesYesYesYesNoYesYesYesNoYesYesN/aYesYesYesYesYesYesNoN/a", then checks if the string 'No' appears in it anywhere

This approach could go wrong if some chance confluence of different values happened to match the string your're looking for - i.e. if the possible answers included (silly example) 'Aye' and 'Sure' as well as Yes, No, etc, Because 'AyeSure' contains the string 'yeS' (granted it probably wouldn't make a difference in this silly example, as they are all affirmative)

Where do i put the formula matey, is it a separate query or a field in a table, dont understand sorry!

 

[simon4amiee]

OK can I add a field the has a calculation along the lines of



If Q1 = No then Fail or if Q2 = No then Fail or if Q3 = No then fail etc etc else pass.



How would i get that forumla into a feild????

 

[gemma-the-husky]

in your query have a new column

NoAnswer: hasno(q1,q2,q3,q4...etc)
in the criteria row put true

then in a module have a function

function hasno(q1,q2,q3,q4 etc) as boolean
hasno = false
if q1="No" then
  hasno = true
  exit function
end if
if q2="No" then
  hasno = true
  exit function
end if
etc

end function

---------------
now what rabbie is saying is

instead of having a table that looks like

user q1 q2 q3 q4 q5 q6 etc
Fred  Y  Y  N  N  n/a  N

you have a table that looks like

user  questionNo  answer
Fred     1             Y
Fred     2             Y
Fred     3             N
Fred     4             N
Fred     5             n/a
etc

and now interrogating the database becomes much easier - dare I say trivial
you dont have to change anything for another questionaire with more or fewer questions, because the data is properly normalised.

------------
you can still arrange to DISPLAY the vertical data in a horizontal form, for the purposes of data entry, although its a bit of work - but the point is that display is really a separate issue from that of data storage.

 

[simon4amiee]

I put that in the field and i cam up with Syntax error (comma) in query expression)

'Questions.[hasno([A1],[A2],[A3],[A4]....ETC, [D3],[D4])]'.

 

[Atomic Shrimp]

Where do i put the formula matey, is it a separate query or a field in a table, dont understand sorry!

No problem. Gemma's solution is better, but some general info on query expressions:

In your queries, you usually just drop in fields from the tables, so you just see the field name in the top row of the design grid.

But you can enter expressions in there too - so instead of having a field named 'Q1' in the top row, you could put Expr1: Q1 & Q2 (actually, if you just type [some field name] & [some other field name] in one of the top row boxes, Access will name the expression Expr1, etc. for you) - when you run the query, you'll see a column headed 'Expr1', filled with the two values joined together.

You can then apply criteria to that expression field exactly as you would if it was a native field in your table.

This particular scenario isn't an ideal example of query expressions - they're more useful when you want to do things like, say, test a date field to see if the recorded date fell in a specific calendar month (Expr1: Month([mydate]) )

 

[simon4amiee]

Mike if its worked your a star, all results that have failed, seemed to have returned -1, all results that have passed have seemed to have retunred 0, ill just check the data now.