Solved

Display Records Horizontally in Access

Posted on 2010-11-15
7
479 Views
Last Modified: 2013-12-25
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
SELECT COMB.Questions
FROM (SELECT EvalID,Q1 as TV,'Q1' as Questions FROM qryEvalScores
UNION
SELECT EvalID,Question02 As TV,'Question02' as Questions FROM qryEvalScores
UNION
SELECT EvalID,Question03 AS TV,'Question03' as Questions FROM qryEvalScores
UNION
SELECT EvalID,Question04 As TV,'Question04' as Questions FROM qryEvalScores AS COMB
GROUP BY COMB.Questions
PIVOT COMB.EvalID;

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.
0
Comment
Question by:Cindy6777
  • 3
  • 2
7 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 34142401
This is the inherent problem with wanting to display data in this fashion...

Here is one solution, ...Happy Reading.
http://support.microsoft.com/kb/328320

;-)

JeffCoachman
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 34142402
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 34142466
Another Option:
(If possible) create a standard "Normalized" table for your data.
At least in this way you can create a simple crosstab query to get the same results.
Then print the Crosstab query directly...?

Simple sample attached, see the table and query
db315.mdb
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 44

Expert Comment

by:GRayL
ID: 34191751
Can you show us the structure of query qryEvalScores
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34191795
How about:

TRANSFORM MAX(COMB.TV) AS MTV
SELECT COMB.EvalID
FROM (SELECT EvalID,Q1 as TV,'Q1' as Questions FROM qryEvalScores
UNION
SELECT EvalID,Question02 As TV,'Question02' as Questions FROM qryEvalScores
UNION
SELECT EvalID,Question03 AS TV,'Question03' as Questions FROM qryEvalScores
UNION
SELECT EvalID,Question04 As TV,'Question04' as Questions FROM qryEvalScores AS COMB
GROUP BY COMB.EvalID
PIVOT COMB.Questions;
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34440900
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now