Solved

Reverse a CrossTab Query

Posted on 2006-10-30
2
944 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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