If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|