query in access 2007 pulls yes only answers

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/...apps&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:

kenwsheridanatyahoodotcodotuk

Ken Sheridan
Stafford, England

Kim T wrote:
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]
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1