Matching rows to columns

G

Guest

I am working on a query based on tables from a linked data source (so no
control over how tables are set up). There are 3 tables involved:
REVIEWS: this gives the name and identifying info for a study
REVIEW CRITERIA: this gives the specific questions (up to 50) for the
particular review. Each question has a NAME ("Q1", "Q2", ...) and a TEXT
field that gives the text to be used for the question
REVIEW ANSWERS: this gives the name of the subject and the answers to the
questions; each record contains all 50 answers for each subject of the study;
field names are just Q1, Q2, Q3, ...
The REVIEW CRITERIA and REVIEW ANSWERS are both linked to the REVIEWS table.

What I need is a result set like this:
[REVIEW NAME] [SUBJECT NAME] [QUESTION 1] [QUESTION 1 ANSWER] [QUESTION 2]
[QUESTION 2 ANSWER] ...

The Problem is that the questions are from rows in the REVIEW CRITERIA table
whereas the answers are columns in the REVIEW ANSWERS table.

I don't want to have to link in 50 copies of the REVIEW CRITERIA table, and
I also don't want to have to use DLookups (slow!); also need to minimize hits
on the external database server - any suggestions?
 
V

Vincent Johns

I'll try to give you enough to get you started...

Suppose your source data look something like these:

[REVIEW CRITERIA] Table Datasheet View:

REVIEW CRI NAME TEXT
TERIA_ID
----------- ---- -----------
-1923671789 Q3 Three
274633081 Q50 Fifty
431385242 Q2 Two
904914333 Q1 One
1050049112 Q49 Forty-nine

[REVIEW ANSWERS] Table Datasheet View:

REVIEW AN Q1 Q2 Q3 Q49 Q50
SWERS_ID
--------- -- -- -- --- ---
636643735 A C A B E
969356206 D A A D E

Then the following Union Query will split out those answers into a more
usable form. It may be long & tedious, but having reformatted the data,
you'll have an easier time analyzing them. Oh, you may notice that I
omitted some of the 50 fields... I'm leaving the rest as an exercise. :)

[Q_010_Answers] SQL:

SELECT RA.[REVIEW ANSWERS_ID],
"Q1" AS [Question Number], RA.Q1
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q2", RA.Q2
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q3", RA.Q3
FROM [REVIEW ANSWERS] AS RA

UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q49", RA.Q49
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q50", RA.Q50
FROM [REVIEW ANSWERS] AS RA
ORDER BY RA.[REVIEW ANSWERS_ID],
[Question Number];

Running this puts all the answers into [Q1] and the question numbers
into [Question Number]. I treated these as text, but you may find that
converting them to numbers will make more sense for your application.

[Q_010_Answers] Query Datasheet View:

REVIEW AN Question Q1
SWERS_ID Number
--------- -------- ---
636643735 Q1 A
636643735 Q2 C
636643735 Q3 A
636643735 Q49 B
636643735 Q50 E
969356206 Q1 D
969356206 Q2 A
969356206 Q3 A
969356206 Q49 D
969356206 Q50 E

The next Query takes these records and links them with the text that you
wanted to see.

[Q_020_AnswersWithText] SQL:

SELECT Q_010_Answers.[REVIEW ANSWERS_ID],
Q_010_Answers.[Question Number],
[REVIEW CRITERIA].TEXT,
Q_010_Answers.Q1
FROM Q_010_Answers
INNER JOIN [REVIEW CRITERIA]
ON Q_010_Answers.[Question Number]
= [REVIEW CRITERIA].NAME
ORDER BY Q_010_Answers.[REVIEW ANSWERS_ID],
Q_010_Answers.[Question Number];


[Q_020_AnswersWithText] Query Datasheet View:

REVIEW AN Question TEXT Q1
SWERS_ID Number
636643735 Q1 One A
636643735 Q2 Two C
636643735 Q3 Three A
636643735 Q49 Forty-nine B
636643735 Q50 Fifty E
969356206 Q1 One D
969356206 Q2 Two A
969356206 Q3 Three A
969356206 Q49 Forty-nine D
969356206 Q50 Fifty E

There are probably various ways to handle putting the answers into a
tabular form like what you asked for. Instead of using separate fields,
I combined the text and the answer for a given question into one string
value and just displayed that. I didn't want to spend lots of time
splitting out separate fields, since I don't know if this will do what
you want, but if you want to do that (and the rest looks good to you),
just ask and someone will show you how.

[Q_030_AnswersXtab] SQL:

TRANSFORM
First([Q_020_AnswersWithText]![TEXT]
& " [" & [Q_020_AnswersWithText]![Q1] & "]")
AS Answer
SELECT Q_020_AnswersWithText.[REVIEW ANSWERS_ID]
FROM Q_020_AnswersWithText
GROUP BY Q_020_AnswersWithText.[REVIEW ANSWERS_ID]
PIVOT Q_020_AnswersWithText.[Question Number];

Of course, displaying all of this in Datasheet View would be
impractical, especially since I assume your [TEXT] fields are way longer
than my example. In a Report, it would be easy to put each on a
separate line on a page, with information about the subject at the top, etc.

[Q_030_AnswersXtab] Query Datasheet View:

REVIEW AN Q1 Q2 Q3 ...
SWERS_ID
--------- ------- ------- ---------
636643735 One [A] Two [C] Three [A] ...
969356206 One [D] Two [A] Three [A] ...

If database performance becomes a problem, you might consider converting
the first Query to a Make-Table Query, putting the results into a
temporary Table for use by the subsequent Queries. (And remember to
delete the Table when you're finished.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


K said:
I am working on a query based on tables from a linked data source (so no
control over how tables are set up). There are 3 tables involved:
REVIEWS: this gives the name and identifying info for a study
REVIEW CRITERIA: this gives the specific questions (up to 50) for the
particular review. Each question has a NAME ("Q1", "Q2", ...) and a TEXT
field that gives the text to be used for the question
REVIEW ANSWERS: this gives the name of the subject and the answers to the
questions; each record contains all 50 answers for each subject of the study;
field names are just Q1, Q2, Q3, ...
The REVIEW CRITERIA and REVIEW ANSWERS are both linked to the REVIEWS table.

What I need is a result set like this:
[REVIEW NAME] [SUBJECT NAME] [QUESTION 1] [QUESTION 1 ANSWER] [QUESTION 2]
[QUESTION 2 ANSWER] ...

The Problem is that the questions are from rows in the REVIEW CRITERIA table
whereas the answers are columns in the REVIEW ANSWERS table.

I don't want to have to link in 50 copies of the REVIEW CRITERIA table, and
I also don't want to have to use DLookups (slow!); also need to minimize hits
on the external database server - any suggestions?
Click to expand...
 
G

Guest

Thanks for the tips. Might be a long query but perhaps I can run some VBA
code to generate the text for me so I don't need to manually get it all
straight! I have thought of using a Make Table as an interim step also but
am trying to set up a system that will be easy for others to use; again I can
code some of it in VBA but hoping to minimize that also since support becomes
difficult with too much custom coding.

Appreciate the help - had not thought of multiple Union queries but that
does make sense.
--
- K Dales


Vincent Johns said:
I'll try to give you enough to get you started...

Suppose your source data look something like these:

[REVIEW CRITERIA] Table Datasheet View:

REVIEW CRI NAME TEXT
TERIA_ID
----------- ---- -----------
-1923671789 Q3 Three
274633081 Q50 Fifty
431385242 Q2 Two
904914333 Q1 One
1050049112 Q49 Forty-nine

[REVIEW ANSWERS] Table Datasheet View:

REVIEW AN Q1 Q2 Q3 Q49 Q50
SWERS_ID
--------- -- -- -- --- ---
636643735 A C A B E
969356206 D A A D E

Then the following Union Query will split out those answers into a more
usable form. It may be long & tedious, but having reformatted the data,
you'll have an easier time analyzing them. Oh, you may notice that I
omitted some of the 50 fields... I'm leaving the rest as an exercise. :)

[Q_010_Answers] SQL:

SELECT RA.[REVIEW ANSWERS_ID],
"Q1" AS [Question Number], RA.Q1
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q2", RA.Q2
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q3", RA.Q3
FROM [REVIEW ANSWERS] AS RA

UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q49", RA.Q49
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q50", RA.Q50
FROM [REVIEW ANSWERS] AS RA
ORDER BY RA.[REVIEW ANSWERS_ID],
[Question Number];

Running this puts all the answers into [Q1] and the question numbers
into [Question Number]. I treated these as text, but you may find that
converting them to numbers will make more sense for your application.

[Q_010_Answers] Query Datasheet View:

REVIEW AN Question Q1
SWERS_ID Number
--------- -------- ---
636643735 Q1 A
636643735 Q2 C
636643735 Q3 A
636643735 Q49 B
636643735 Q50 E
969356206 Q1 D
969356206 Q2 A
969356206 Q3 A
969356206 Q49 D
969356206 Q50 E

The next Query takes these records and links them with the text that you
wanted to see.

[Q_020_AnswersWithText] SQL:

SELECT Q_010_Answers.[REVIEW ANSWERS_ID],
Q_010_Answers.[Question Number],
[REVIEW CRITERIA].TEXT,
Q_010_Answers.Q1
FROM Q_010_Answers
INNER JOIN [REVIEW CRITERIA]
ON Q_010_Answers.[Question Number]
= [REVIEW CRITERIA].NAME
ORDER BY Q_010_Answers.[REVIEW ANSWERS_ID],
Q_010_Answers.[Question Number];


[Q_020_AnswersWithText] Query Datasheet View:

REVIEW AN Question TEXT Q1
SWERS_ID Number
636643735 Q1 One A
636643735 Q2 Two C
636643735 Q3 Three A
636643735 Q49 Forty-nine B
636643735 Q50 Fifty E
969356206 Q1 One D
969356206 Q2 Two A
969356206 Q3 Three A
969356206 Q49 Forty-nine D
969356206 Q50 Fifty E

There are probably various ways to handle putting the answers into a
tabular form like what you asked for. Instead of using separate fields,
I combined the text and the answer for a given question into one string
value and just displayed that. I didn't want to spend lots of time
splitting out separate fields, since I don't know if this will do what
you want, but if you want to do that (and the rest looks good to you),
just ask and someone will show you how.

[Q_030_AnswersXtab] SQL:

TRANSFORM
First([Q_020_AnswersWithText]![TEXT]
& " [" & [Q_020_AnswersWithText]![Q1] & "]")
AS Answer
SELECT Q_020_AnswersWithText.[REVIEW ANSWERS_ID]
FROM Q_020_AnswersWithText
GROUP BY Q_020_AnswersWithText.[REVIEW ANSWERS_ID]
PIVOT Q_020_AnswersWithText.[Question Number];

Of course, displaying all of this in Datasheet View would be
impractical, especially since I assume your [TEXT] fields are way longer
than my example. In a Report, it would be easy to put each on a
separate line on a page, with information about the subject at the top, etc.

[Q_030_AnswersXtab] Query Datasheet View:

REVIEW AN Q1 Q2 Q3 ...
SWERS_ID
--------- ------- ------- ---------
636643735 One [A] Two [C] Three [A] ...
969356206 One [D] Two [A] Three [A] ...

If database performance becomes a problem, you might consider converting
the first Query to a Make-Table Query, putting the results into a
temporary Table for use by the subsequent Queries. (And remember to
delete the Table when you're finished.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


K said:
I am working on a query based on tables from a linked data source (so no
control over how tables are set up). There are 3 tables involved:
REVIEWS: this gives the name and identifying info for a study
REVIEW CRITERIA: this gives the specific questions (up to 50) for the
particular review. Each question has a NAME ("Q1", "Q2", ...) and a TEXT
field that gives the text to be used for the question
REVIEW ANSWERS: this gives the name of the subject and the answers to the
questions; each record contains all 50 answers for each subject of the study;
field names are just Q1, Q2, Q3, ...
The REVIEW CRITERIA and REVIEW ANSWERS are both linked to the REVIEWS table.

What I need is a result set like this:
[REVIEW NAME] [SUBJECT NAME] [QUESTION 1] [QUESTION 1 ANSWER] [QUESTION 2]
[QUESTION 2 ANSWER] ...

The Problem is that the questions are from rows in the REVIEW CRITERIA table
whereas the answers are columns in the REVIEW ANSWERS table.

I don't want to have to link in 50 copies of the REVIEW CRITERIA table, and
I also don't want to have to use DLookups (slow!); also need to minimize hits
on the external database server - any suggestions?
Click to expand...
Click to expand...
 
V

Vincent Johns

Or you might try generating the code using Excel and copy and paste the
results. You could use Excel code similar to

="UNION ALL SELECT RA.[REVIEW ANSWERS_ID], Q"
& A3 & ", RA.Q" & A3 & " FROM [REVIEW ANSWERS] AS RA "

to generate the all the statements you need pretty quickly.

Even with a Make-Table Query (if necessary to improve performance), you
can make the Query easy for others to use by putting it into a Macro.
The Macro can be kept pretty short.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.



K said:
Thanks for the tips. Might be a long query but perhaps I can run some VBA
code to generate the text for me so I don't need to manually get it all
straight! I have thought of using a Make Table as an interim step also but
am trying to set up a system that will be easy for others to use; again I can
code some of it in VBA but hoping to minimize that also since support becomes
difficult with too much custom coding.

Appreciate the help - had not thought of multiple Union queries but that
does make sense.
--
- K Dales

Vincent Johns said:
I'll try to give you enough to get you started...

Suppose your source data look something like these:

[REVIEW CRITERIA] Table Datasheet View:

REVIEW CRI NAME TEXT
TERIA_ID
----------- ---- -----------
-1923671789 Q3 Three
274633081 Q50 Fifty
431385242 Q2 Two
904914333 Q1 One
1050049112 Q49 Forty-nine

[REVIEW ANSWERS] Table Datasheet View:

REVIEW AN Q1 Q2 Q3 Q49 Q50
SWERS_ID
--------- -- -- -- --- ---
636643735 A C A B E
969356206 D A A D E

Then the following Union Query will split out those answers into a more
usable form. It may be long & tedious, but having reformatted the data,
you'll have an easier time analyzing them. Oh, you may notice that I
omitted some of the 50 fields... I'm leaving the rest as an exercise. :)

[Q_010_Answers] SQL:

SELECT RA.[REVIEW ANSWERS_ID],
"Q1" AS [Question Number], RA.Q1
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q2", RA.Q2
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q3", RA.Q3
FROM [REVIEW ANSWERS] AS RA

UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q49", RA.Q49
FROM [REVIEW ANSWERS] AS RA
UNION ALL
SELECT RA.[REVIEW ANSWERS_ID], "Q50", RA.Q50
FROM [REVIEW ANSWERS] AS RA
ORDER BY RA.[REVIEW ANSWERS_ID],
[Question Number];

Running this puts all the answers into [Q1] and the question numbers
into [Question Number]. I treated these as text, but you may find that
converting them to numbers will make more sense for your application.

[Q_010_Answers] Query Datasheet View:

REVIEW AN Question Q1
SWERS_ID Number
--------- -------- ---
636643735 Q1 A
636643735 Q2 C
636643735 Q3 A
636643735 Q49 B
636643735 Q50 E
969356206 Q1 D
969356206 Q2 A
969356206 Q3 A
969356206 Q49 D
969356206 Q50 E

The next Query takes these records and links them with the text that you
wanted to see.

[Q_020_AnswersWithText] SQL:

SELECT Q_010_Answers.[REVIEW ANSWERS_ID],
Q_010_Answers.[Question Number],
[REVIEW CRITERIA].TEXT,
Q_010_Answers.Q1
FROM Q_010_Answers
INNER JOIN [REVIEW CRITERIA]
ON Q_010_Answers.[Question Number]
= [REVIEW CRITERIA].NAME
ORDER BY Q_010_Answers.[REVIEW ANSWERS_ID],
Q_010_Answers.[Question Number];


[Q_020_AnswersWithText] Query Datasheet View:

REVIEW AN Question TEXT Q1
SWERS_ID Number
636643735 Q1 One A
636643735 Q2 Two C
636643735 Q3 Three A
636643735 Q49 Forty-nine B
636643735 Q50 Fifty E
969356206 Q1 One D
969356206 Q2 Two A
969356206 Q3 Three A
969356206 Q49 Forty-nine D
969356206 Q50 Fifty E

There are probably various ways to handle putting the answers into a
tabular form like what you asked for. Instead of using separate fields,
I combined the text and the answer for a given question into one string
value and just displayed that. I didn't want to spend lots of time
splitting out separate fields, since I don't know if this will do what
you want, but if you want to do that (and the rest looks good to you),
just ask and someone will show you how.

[Q_030_AnswersXtab] SQL:

TRANSFORM
First([Q_020_AnswersWithText]![TEXT]
& " [" & [Q_020_AnswersWithText]![Q1] & "]")
AS Answer
SELECT Q_020_AnswersWithText.[REVIEW ANSWERS_ID]
FROM Q_020_AnswersWithText
GROUP BY Q_020_AnswersWithText.[REVIEW ANSWERS_ID]
PIVOT Q_020_AnswersWithText.[Question Number];

Of course, displaying all of this in Datasheet View would be
impractical, especially since I assume your [TEXT] fields are way longer
than my example. In a Report, it would be easy to put each on a
separate line on a page, with information about the subject at the top, etc.

[Q_030_AnswersXtab] Query Datasheet View:

REVIEW AN Q1 Q2 Q3 ...
SWERS_ID
--------- ------- ------- ---------
636643735 One [A] Two [C] Three [A] ...
969356206 One [D] Two [A] Three [A] ...

If database performance becomes a problem, you might consider converting
the first Query to a Make-Table Query, putting the results into a
temporary Table for use by the subsequent Queries. (And remember to
delete the Table when you're finished.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


K Dales wrote:
Click to expand...
I am working on a query based on tables from a linked data source (so no
control over how tables are set up). There are 3 tables involved:
REVIEWS: this gives the name and identifying info for a study
REVIEW CRITERIA: this gives the specific questions (up to 50) for the
particular review. Each question has a NAME ("Q1", "Q2", ...) and a TEXT
field that gives the text to be used for the question
REVIEW ANSWERS: this gives the name of the subject and the answers to the
questions; each record contains all 50 answers for each subject of the study;
field names are just Q1, Q2, Q3, ...
The REVIEW CRITERIA and REVIEW ANSWERS are both linked to the REVIEWS table.

What I need is a result set like this:
[REVIEW NAME] [SUBJECT NAME] [QUESTION 1] [QUESTION 1 ANSWER] [QUESTION 2]
[QUESTION 2 ANSWER] ...

The Problem is that the questions are from rows in the REVIEW CRITERIA table
whereas the answers are columns in the REVIEW ANSWERS table.

I don't want to have to link in 50 copies of the REVIEW CRITERIA table, and
I also don't want to have to use DLookups (slow!); also need to minimize hits
on the external database server - any suggestions?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

formula need a fix. HELP! 1
"variable" column name 4
Query to show latest review only 4
Average multiple colums 5
Not display information if criteria is not met 2
Count field entries selectively 4
Need help to structure simple (I think) query... 2
Summarize Answers via a Query 3

Top