G
Guest
I have a table (Table1) with questions: each row has a question number
(1,2,3,4...) and the text (e.g. "Date of Procedure", "Procedure", "MD Name",
"Start Time", ...). A separate table (Table2) contains the answers: in this
table each row is for a particular case and has columns named Q1, Q2, Q3, Q4,
etc... that contain the answers to the corresponding questions. There are
several sets of these for different studies. Each study will have its own
list of questions (stored as a row in Table1); Table2 has a link to the study
number and then gives the case information and answers to each question. The
number of questions varies for each study, anywhere up to 50 questions are
allowed. Table2 has columns Q1, Q2, Q3... Q50; any question that is not
used in a particular study is Null in Table2.
I am trying to get a query that allows me to choose a question and show that
question with its corresponding answers, e.g:
Case#1 Date of Procedure: 3/5/2006
Case#2 Date of Procedure: 3/8/2006
....
I don't want to have 50 different queries and I don't want to have to hard
code them because studies might change. I also want to avoid using any
DLookups because the result set here will be pretty large and any Domain
Aggregate functions will make this slow to a crawl.
My thought is to try to develop an expression that can select a column from
an index number: for example, if I select question number 5 from Table1, the
corresponding answer is in the column [Table2].[Q5]. If there was a function
called ChooseColumn that took text input and returned the column value having
that name I could have a calculated field in the query:
ANSWER:ChooseColumn("[Table2].[Q"&[Table1].[question]&"]")
But, as far as I know, there is no such function and although I have quite a
bit of VBA knowhow I am stumped on trying to write a user function that would
do this without having to hit the database again with a search that would
slow down execution.
Has anyone out there ever tried anything like this?
(1,2,3,4...) and the text (e.g. "Date of Procedure", "Procedure", "MD Name",
"Start Time", ...). A separate table (Table2) contains the answers: in this
table each row is for a particular case and has columns named Q1, Q2, Q3, Q4,
etc... that contain the answers to the corresponding questions. There are
several sets of these for different studies. Each study will have its own
list of questions (stored as a row in Table1); Table2 has a link to the study
number and then gives the case information and answers to each question. The
number of questions varies for each study, anywhere up to 50 questions are
allowed. Table2 has columns Q1, Q2, Q3... Q50; any question that is not
used in a particular study is Null in Table2.
I am trying to get a query that allows me to choose a question and show that
question with its corresponding answers, e.g:
Case#1 Date of Procedure: 3/5/2006
Case#2 Date of Procedure: 3/8/2006
....
I don't want to have 50 different queries and I don't want to have to hard
code them because studies might change. I also want to avoid using any
DLookups because the result set here will be pretty large and any Domain
Aggregate functions will make this slow to a crawl.
My thought is to try to develop an expression that can select a column from
an index number: for example, if I select question number 5 from Table1, the
corresponding answer is in the column [Table2].[Q5]. If there was a function
called ChooseColumn that took text input and returned the column value having
that name I could have a calculated field in the query:
ANSWER:ChooseColumn("[Table2].[Q"&[Table1].[question]&"]")
But, as far as I know, there is no such function and although I have quite a
bit of VBA knowhow I am stumped on trying to write a user function that would
do this without having to hit the database again with a search that would
slow down execution.
Has anyone out there ever tried anything like this?