Change data in column to row fields

[jcarstens]

I have a query in a database that I am trying switch the data from column 1 to be the fields for the query.



I have attached an example.

Query1AVG has the following fields:

DateRange, AvgOfQuestion1, AvgOfQuestion2, AvgOfQuestion3, AvgOfQuestion4, AvgOfQuestion5

With data as follows:

Range1, some #, some #, some #, some #, some #
Range2, some #, some #, some #, some #, some #
Range3, some #, some #, some #, some #, some #
Range4, some #, some #, some #, some #, some #
Range5, some #, some #, some #, some #, some #

I am trying to figure out how to swap the row/column to achieve the following layout:

Fields: Range1, Range2, Range3, Range4, Range5

With data being:

AvgOfQuestion1, some#, some #, some #, Some #, some #
AvgOfQuestion2, some#, some #, some #, Some #, some #
AvgOfQuestion3, some#, some #, some #, Some #, some #
AvgOfQuestion4, some#, some #, some #, Some #, some #
AvgOfQuestion5, some#, some #, some #, Some #, some #

This will be a query which will populate a report, so will need to be run frequently.

Any help on this is greatly appreciatedDatabase2.accdb.zip

[June7]

This would be easier if the data in Table1 was structured like:

TestDate	QuestionData	QuestionID
12/5/2012	23	1
12/5/2012	12	5
12/5/2012	65	3
12/5/2012	54	2
12/5/2012	23	4

Then could do the table join and a CROSSTAB query.

Your data can be rearranged into this structure with a UNION query.

SELECT ID, TestDate, "Question1" As QuestionID, Question1 As QuestionData FROM Table1
UNION SELECT ID, TestDate, "Question2", Question2 FROM Table1
UNION SELECT ID, TestDate, "Question3", Question3 FROM Table1
UNION SELECT ID, TestDate, "Question4", Question4 FROM Table1
UNION SELECT ID, TestDate, "Question5", Question5 FROM Table1;