Solved

Reverse a CrossTab Query

Posted on 2006-10-30
2
925 Views
Last Modified: 2008-04-21
Her is my data:

REASON            10/29/06      10/28/06      10/27/06
15            0      1      10
CANCEL_1            5      5      14
CANCEL_2            36      22      79
CANCEL_3            40      36      74


I want to change it and move it around so it looks like this:

REASON      DAY      VALUES
15      10/29/06      0
CANCEL_1      10/29/06      5
CANCEL_2      10/29/06      36
CANCEL_3      10/29/06      40
15      10/28/06      1
CANCEL_1      10/28/06      5
CANCEL_2      10/28/06      22
CANCEL_3      10/28/06      36
15      10/27/06      10
CANCEL_1      10/27/06      14
CANCEL_2      10/27/06      79
CANCEL_3      10/27/06      74

This is like reversing a crosstab in ACCESS.  

How do I do this? Union Query ? Cross Tab?  Can I use a Query Wizard in ACCESS?
0
Comment
Question by:glenart10
2 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 150 total points
ID: 17838021
Hi,

I'd do

Select
   Reason, "10/29/06" as Day, [10/29/06] as Values
From tableName
union
Select
   Reason, "10/28/06" as Day, [10/28/06] as Values
From tableName
union
Select
   Reason, "10/27/06" as Day, [10/27/06] as Values
From tableName

unfortunatley there is no tool to do this for you - so you would have to rely on union queries.
0
 

Author Comment

by:glenart10
ID: 17838940
I never had used SQL Viewer while in ACCESS.  

I had to use it here.  This code worked well except I cannot use the FIELD name, VALUES.  That name is a reserved field name and I had to change it.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

17 Experts available now in Live!

Get 1:1 Help Now