"variable" column name

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

 

[Douglas J. Steele]

Sorry if I sound abrupt, but your table is not designed properly. Having
columns named Q1, Q2 etc. is wrong. Instead, each answer should be a
separate row, just as the Question table is.

 

[Guest]

Yes, I agree 100%; but I did not design the table and have no control over
that!
--
- K Dales

Sorry if I sound abrupt, but your table is not designed properly. Having
columns named Q1, Q2 etc. is wrong. Instead, each answer should be a
separate row, just as the Question table is.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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?

 

[Douglas J. Steele]

In that case, you're probably out of luck: 50 separate queries may be
necessary.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, I agree 100%; but I did not design the table and have no control over
that!
--
- K Dales

Sorry if I sound abrupt, but your table is not designed properly. Having
columns named Q1, Q2 etc. is wrong. Instead, each answer should be a
separate row, just as the Question table is.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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?

 

[Guest]

Thanks anyway for giving it some consideration. I am groping for an easy
solution but there may not be one.

I am using tables linked in from a 3rd party application; I can only read
the tables and have to live with the design flaws.

One other bit of fun I ran across: all the answers (Q1, Q2, ....) are stored
as text, although some of them are foreign keys to lookup tables - with
numeric (long) key values! So I can't link directly to the actual "answer",
I need to convert to long first.

Sometimes I look at the database structures in these "professional" apps and
just shake my head in disbelief.

Sorry for the rant...
--
- K Dales

In that case, you're probably out of luck: 50 separate queries may be
necessary.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, I agree 100%; but I did not design the table and have no control over
that!
--
- K Dales

Sorry if I sound abrupt, but your table is not designed properly. Having
columns named Q1, Q2 etc. is wrong. Instead, each answer should be a
separate row, just as the Question table is.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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?