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

 

[KenSheridan via AccessMonster.com]

You will need three tables. What you have are two entity types Patients and
Questions, and a many-to-many relationship between them, i.e. each patient
can answer many questions, and each question can be answered by many patients.
The many-to-many relationship is modelled by the third table, Answers, which
would have three columns, SS#, Question and Answer, the last being a Boolean
(Yes/no) data type if the answers are all either yes or no. Make the primary
key of this table a composite one of SS# and Question, which you do in table
design view by Ctrl-clicking on each field, making sure you click on the
field selector (the little grey rectangle to the left of the field name),
then right-click and select 'Primary key' from the shortcut menu.
This means you can't have each patient answering the same question twice.

I'd suggest also including a QuestionNumber field in the Questions table so
that you can sort the questions by that number.

For data entry you would probably have a Patients form based on the Patients
table, and within that an Answers subform based on the Answers table. The
subform would be linked to the parent form on SS# and would be in continuous
forms view, and contain a combo box bound to the Question field and a check
box bound to the Answer field. The RowSource of the combo box would be:

SELECT Question FROM Questions ORDER BY QuestionNumber;

For each question per patient a new row is inserted in the subform, selecting
the question form the combo box's list and checking the answer check box if
the answer is yes.

A query for a report would be along these lines:

SELECT [Patients].[SS#], [Patients].[Name],
[Questions].[QuestionNumber], [Questions].[Question]
FROM [Patients], [Answers], [Questions]
WHERE [Patients].[SS#] = [Answers].[SS#]
AND [Answers].[Question] = [Questions].[Question]
AND [Answers].[Answer] = TRUE;

In report design view group the report first by patient name, then by ss#,
and then by question number, and give the ss# group a group header. Include
the patient data in the group header and the question data in the detail
section.

This would list the 'yes' answers per patient vertically. If you want them
spread across the page to reduce the space used then create a report based on
patients and an 'across then down' multi-column subreport based on a query
which joins Answers and Questions. You'll find an example of this sort of
report/subreport layout at:

http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


The file also includes a way of doing it in a single report manipulating the
layout in code at runtime, but I only included that to show that it was
possible to do it that way (the file was originally produced in response to a
question by a reader of a magazine column written by a contact of mine). The
report/subreport solution is far simpler and needs no code.

That's a very simple model as the questions all have yes/no answers. With a
more complex model, where you might have different possible answers to each
question then it gets more complex as you have to introduce further tables.
Duane Hookom's database to which Jerry referred you demonstrates one possible
approach. As it happens I've recently put together a database for
constructing questionnaires myself, mainly for demonstration purposes, though
it does provide a working solution to most common questionnaire requirements
in itself. Its not as yet available online, but if you'd like a copy mail me
at:

kenwsheridan<at>yahoo<dot>co<dot>uk

Ken Sheridan
Stafford, England

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# ?

I hope so! ;-)

[quoted text clipped - 68 lines]