Solved

ms access transpose columns and values to rows

Posted on 2014-12-31
3
1,566 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
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40525581
glad I could help
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro to import XML in Access 2013 2 40
Modal form 11 30
Allow user to edit an outgoing email 2 25
SQL multicriteria from ONE textbox 32 44
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…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
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…

822 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