query in access 2007 pulls yes only answers

[Kim T]

I've got a form that has Y/N questions I want to make a query that only pulls
the yes answers then turn that into a report need help please.

 

[Tom van Stiphout]

On Thu, 24 Dec 2009 05:18:06 -0800, Kim T

Create a new query. Select your fields in the grid. In the column for
your Y/N field in the criteria line type "Yes".

-Tom.
Microsoft Access MVP

 

[Jerry Whittle]

You first need to find the table that's used to store the data that you see
in the form.

Open up that table and look at the columns.
-- Is there only one Yes/No column or are there a bunch of them?
-- In the table design, are these actual Yes/No data types or text fields
with "Yes" or "No" in them.

Next you need to know exactly what you want on the report. Assuming that
they are Yes/No data types AND there are many Yes/No columns, what do you
want? For example:

Name Blue Red
Tim Yes No
Jim No Yes
Slim Yes Yes
Ron No No

From the above would you want to see everything from everyone but Ron, or
only the Yes answers something like:

Tim Blue Yes
Jim Red Yes
Slim Blue Yes
Slim Red Yes

The table and field names would also help us help you.

 

[Jerry Whittle]

Hi Tom,

If it's an actual Yes/No data type, you don't want the quotation marks.
You'll get a data type mismatch error. Actually -1 works best for Yes and 0
for No.

I'm also thinking that the problem may be with multiple Yes/No fields in the
table. Therefore all the questions in my other answer.

 

[Kim T]

I've have 1 table with 34 questions. each question has data type being Yes/No

 

[Jerry Whittle]

(My apologies if this gets posted more than once. Something strange happened
during the first attempts.)

Sorry to hear that. The proper table structure would be down and not across.
It would look something like:

Name Question Answer
Jim Blue No
Jim Red Yes
-- and so on. Then a simple query something like this would do the job.

SELECT Name, Question, Answer
FROM YourTable
WHERE Answer = Yes ;

To deal with your existing table structure and depending on how you want to
see the data, it will probably take 34 queries joined by UNION ALL. Using my
little table example below, the SQL statement would look like:

Select Name, "Blue" as TheQuestion, "YES" as TheAnswer
FROM YourTableName
WHERE Blue = Yes
UNION ALL
Select Name, "Red" as TheQuestion, "YES" as TheAnswer
FROM YourTableName
WHERE RED = Yes
UNION ALL
-- for all 34 questions.

The "Blue" and "Red" would put those words in the records returned. The
"YES" does the same although you might not need it as you are only looking
for Yes answers.

Once you get it to run, you could create a report based on the above query.

 

[Kim T]

thankyou I will try this on Tuesday getting ready to leave for the holiday.
will you be around tuesday if I need help?

 

[Jerry Whittle]

I hope so! ;-)

 

[Kim T]

should I make three tables then:
Patient (Name, SS#, Date)
Question (list each)
Answer (make this a Yes/No or text with yes/no)
Relate all 3 together by SS# ?

 

[Jerry Whittle]

You might want to look at Duane Hookom's At Your Survey database example. It
can be found at:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3