G
Guest
I am working in Access 2000. I am creating a database to record Yes/No/NA
answers to 92 questions of an assessment. The purpose of the database is to
provide standardized recommendations to any questions answered with a "No".
The 92 questions are grouped in 12 category sections. I have created a main
form with 12 subforms for users to input the answers and any comments
specific to the questions in the appropriate subforms. Each subform records
the answers for that section of questions in their own table (so I have 12
tables of answers). I also have a table (TBL_Questions) that stores the
section number, question number, 92 questions and recommended action for each
question.
The form and subforms are working correctly. But now I have run into a
wall. I need to create a report that displays the answer for each question
(whether it was answered Yes, No or NA) and then display the recommended
action (stored in TBL_Questions) if a question was answered "No". I created
a query to display all the questions answers from the 12 tables for each
subform, but I can't join the TBL_Question to any other table in the
database, because the TBL_Question stores each question in a row, but the
tables that store the data entered in subforms stores each answer in it's own
column, so there is not a column heading on the two tables that matches.
Table for subform:
columns: A1_1 (Answer for section1, question 1)
C1_1 (Comment for section1, question 1)
A1_2
C1_2
A1_3
C1_3
....etc.
Rows: adds a new record for each assessment completed (stores assessment by
primary key of date&facility address).
Table for Questions:
Columns: SectionNo
QuestionNo
Question
Recommendation
I tried adding a column for "Section Number" in the subform table, to see if
I could atleast relate the table by sections, but that doesn't tell it that
if the answer stored in column A1_4 is No, display the recommendation for
record number 4 of the Question table. How do I join these two tables
together?
answers to 92 questions of an assessment. The purpose of the database is to
provide standardized recommendations to any questions answered with a "No".
The 92 questions are grouped in 12 category sections. I have created a main
form with 12 subforms for users to input the answers and any comments
specific to the questions in the appropriate subforms. Each subform records
the answers for that section of questions in their own table (so I have 12
tables of answers). I also have a table (TBL_Questions) that stores the
section number, question number, 92 questions and recommended action for each
question.
The form and subforms are working correctly. But now I have run into a
wall. I need to create a report that displays the answer for each question
(whether it was answered Yes, No or NA) and then display the recommended
action (stored in TBL_Questions) if a question was answered "No". I created
a query to display all the questions answers from the 12 tables for each
subform, but I can't join the TBL_Question to any other table in the
database, because the TBL_Question stores each question in a row, but the
tables that store the data entered in subforms stores each answer in it's own
column, so there is not a column heading on the two tables that matches.
Table for subform:
columns: A1_1 (Answer for section1, question 1)
C1_1 (Comment for section1, question 1)
A1_2
C1_2
A1_3
C1_3
....etc.
Rows: adds a new record for each assessment completed (stores assessment by
primary key of date&facility address).
Table for Questions:
Columns: SectionNo
QuestionNo
Question
Recommendation
I tried adding a column for "Section Number" in the subform table, to see if
I could atleast relate the table by sections, but that doesn't tell it that
if the answer stored in column A1_4 is No, display the recommendation for
record number 4 of the Question table. How do I join these two tables
together?