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 |
#1
|
|||
|
|||
"variable" column name
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 Procedu 3/5/2006 Case#2 Date of Procedu 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? -- - K Dales |
#2
|
|||
|
|||
"variable" column name
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 http://I.Am/DougSteele (no private e-mails, please) "K Dales" wrote in message ... 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 Procedu 3/5/2006 Case#2 Date of Procedu 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? -- - K Dales |
#3
|
|||
|
|||
"variable" column name
Yes, I agree 100%; but I did not design the table and have no control over
that! -- - K Dales "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "K Dales" wrote in message ... 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 Procedu 3/5/2006 Case#2 Date of Procedu 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? -- - K Dales |
#4
|
|||
|
|||
"variable" column name
In that case, you're probably out of luck: 50 separate queries may be
necessary. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "K Dales" wrote in message ... Yes, I agree 100%; but I did not design the table and have no control over that! -- - K Dales "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "K Dales" wrote in message ... 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 Procedu 3/5/2006 Case#2 Date of Procedu 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? -- - K Dales |
#5
|
|||
|
|||
"variable" column name
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 "Douglas J. Steele" wrote: In that case, you're probably out of luck: 50 separate queries may be necessary. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "K Dales" wrote in message ... Yes, I agree 100%; but I did not design the table and have no control over that! -- - K Dales "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "K Dales" wrote in message ... 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 Procedu 3/5/2006 Case#2 Date of Procedu 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? -- - K Dales |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mozilla Firefox problem Please Hejp | shen923 | Publisher | 8 | May 16th, 2006 08:16 AM |
Conditional Format as a MACRO | Gunjani | Worksheet Functions | 3 | March 29th, 2006 05:22 PM |
Return SEARCHED Column Number of Numeric Label and Value | Sam via OfficeKB.com | Worksheet Functions | 23 | January 30th, 2006 06:16 PM |
match and count words | David | Worksheet Functions | 5 | July 4th, 2005 02:24 AM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |