Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ms access transpose columns and values to rows

Posted on 2014-12-31
3
Medium Priority
?
2,237 Views
Last Modified: 2014-12-31
Hello!

I have a query that has the following structure:
Date           Dim01   Dim02    Dim03    Dim04
12/31/14    0.123     1.234      0.567      2.987
01/05/15    0.126     1.287      0.611      2.888

Is it possible to make a crosstab query (or any type) to get an output like this?
Date           Dim        Value
12/31/14    Dim01    0.123
12/31/14    Dim02    1.234
12/31/14    Dim03    0.567
12/31/14    Dim04    2.987
01/05/15    Dim01    0.126
01/05/15    Dim02    1.287
01/05/15    Dim03    0.611
01/05/15    Dim04    2.888

The ultimate goal is to produce a report and graph that will show the variation for an individual Dimension over time.

Any and all help and suggestions are appreciated.  Thanks for your time and have a great new year!
0
Comment
Question by:jeff-h
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40525456
If your query looks like (which looks like a crosstab):

Date           Dim01   Dim02    Dim03    Dim04
12/31/14    0.123     1.234      0.567      2.987
01/05/15    0.126     1.287      0.611      2.888

What does the actual data already look like?  It would probably be easier to change the query you have than to "normalize" the crosstab query.  However, the technique for normalizing a non-normal table like this is a union query:

SELECT [yourDate], "Dim01" as Dimension, [Dim01] as DimValue FROM yourQuery
UNION ALL
SELECT [yourDate], "Dim02" as Dimension, [Dim02] as DimValue FROM yourQuery
UNION ALL
SELECT [yourDate], "Dim03" as Dimension, [Dim03] as DimValue FROM yourQuery
UNION ALL
SELECT [yourDate], "Dim04" as Dimension, [Dim04] as DimValue FROM yourQuery

BTW, [Date], [Dim], and [Value] are reserved words in Access, so if you want to use those as column headers, you will have to wrap them in brackets.  Personally, I prefer to change the column headers as I did above.
0
 

Author Closing Comment

by:jeff-h
ID: 40525507
Absolutely fantastic, thanks a million Dale!
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40525581
glad I could help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

705 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