Display Records Horizontally in Access
Posted on 2010-11-15
I want to display the records of an evaluation table horizontally instead of vertically. Through an answer from this website (a really long time ago) I was able to create an SQL query that did just that. Here is a sample:
EvalID Q1 Question02 Question03 Question04
244 4 4 4 4
245 5 5 4 5
246 5 5 5 5
247 5 5 5 5
When I run the SQL, it appears like this:
Questions 244 245 246 247
Question01 4 5 5 5
Question02 4 5 5 5
Question03 4 4 5 5
Question04 4 5 5 5
The 244, 245 etc are the EvalID that will change every time the query is run.
Here is the code behind the SQL:
TRANSFORM MAX(COMB.TV) AS MTV
FROM (SELECT EvalID,Q1 as TV,'Q1' as Questions FROM qryEvalScores
SELECT EvalID,Question02 As TV,'Question02' as Questions FROM qryEvalScores
SELECT EvalID,Question03 AS TV,'Question03' as Questions FROM qryEvalScores
SELECT EvalID,Question04 As TV,'Question04' as Questions FROM qryEvalScores AS COMB
GROUP BY COMB.Questions
It works great in the query. The problem is, I would like to build a report but I can't because the EvalIDs will change each time rendering any textboxes in the report obsolete.
I think what I need to do is put the SQL into VB to recreate the report each time, I am just not sure how to do it.
Any help would be appreciated.