Columns to Rows in MS Access

Question

I am trying to create a query in MS Access to ultimately take the output from this:

Name        Cat 1   Cat 2   Cat 3   Cat 4   Cat 5   Cat 6
Joe         2       12      10      1       0       0
Bob         0       0       0       0       0       0
Jody        2       4       3       1       2       0
Harry       0       4       14      0       2       0

To something like this:

Name        Joe     Bob     Jody    Harry
Cat 1       2       0       2       0
Cat 2       12      0       4       4
Cat 3       10      0       3       14

Is this even possible?

EDIT

SELECT [Authorizer Name], 
Sum([Q1A - CD # 1]) AS [Category 1], 
Sum([Q2A- CD # 2A] + [Q8A- CD # 2A] + [Q10A- CD # 2A] 
    + [CTS A accurate- CD # 2A] 
    + [e-correspondence A accurate- CD # 2A]) AS [Category 2], 
Sum([Q7A- CD # 2B] + [Q9A- CD # 2B] + [Q11A- CD # 2B] 
    + [CTS A procedures- CD # 2B] 
    + [e-correspondence A procedures- CD # 2B]) AS [Category 3], 
Sum([Q4A- CD # 3]) AS [Category 4], 
Sum([Q5A- CD # 4]) AS [Category 5], Sum([Q12A- CD # 5]) AS [Category 6] 
FROM [Review Results] 
WHERE [Review Results].[Authorizer Name] = 1 
GROUP BY [Review Results].[Authorizer Name];

This is the way the table currently is:

Name    X1 X2A   X2B X2C X3A X3B X3C X4 X5 
Joe     1  5     0   1   1   5   6   0  0
Bob     2  7     0   2   1   4   2   1  9 
Billy   0  8     0   3   1   3   1   0  9

This is what I would like to get to:

Name     Joe                  Bob    Billy 
X1       1                     2         0 
X2 (sum of X2A/X2B/X2C) 6      9         11 
X3 (sum of X3A/X3B/X3C) 12     7         5 
X4       0                     1         0 
X5       0                     9         9

So can you tell me why the field names in the sample data are completely different to the field names in your query? — Fionnuala, Feb 08 '12 at 14:38
Hi, sorry I am having troubles copying and pasting to a proper table format. The query I sent you references the actual fields/columns in my database. However, the real data set contains sensitive information. So I've drafted the "mock" tables as you see above. The tables and query are not related to each other. Hope this helps? — JT2013, Feb 08 '12 at 14:42
Let me guess, you have sent up a questionnaire type table with the questions running across? You need to start normalizing at table level. Give me a few minutes. — Fionnuala, Feb 08 '12 at 14:47
Your exactly right! The person who originally designed the database did not normalize.. thanks again — JT2013, Feb 08 '12 at 14:52
Do yourself a favour and create a normalized table using make table on the union query below and do any further analysis on that. — Fionnuala, Feb 08 '12 at 15:00

Fionnuala · Accepted Answer · 2012-02-20T10:18:22.237

4

First create a UNION query to normalize your table, then a crosstab to display the data:

SELECT [Name], "Cat 1" As Cat, [Cat 1] As CatVal FROM Table
UNION ALL
SELECT [Name], "Cat 2" As Cat, [Cat 2] As CatVal FROM Table
<...>

The query wizard will guide you through creating the crosstab.

EDIT re comments

To normalize the table of sample data, you can say:

SELECT [Name], "X1" As Cat, [X1] As CatVal FROM Table
UNION ALL
SELECT [Name], "X2" As Cat, Nz([X2A],0)+Nz([X2B],0)+Nz([X2C],0) As CatVal 
   FROM Table
UNION ALL
SELECT [Name], "X3" As Cat, Nz([X3A],0)+Nz([X3B],0)+Nz([X3C],0) As CatVal 
   FROM Table
UNION ALL
SELECT [Name], "X4" As Cat, [X4] As CatVal FROM Table
UNION ALL
SELECT [Name], "X5" As Cat, [X5] As CatVal FROM Table

Let us say the union query is QueryX:

TRANSFORM First(QueryX.CatVal) AS FirstOfCatVal
SELECT QueryX.Cat
FROM QueryX
GROUP BY QueryX.Cat
PIVOT QueryX.Name;

edited Feb 20 '12 at 10:18

answered Feb 07 '12 at 21:08

Fionnuala

90,370
7
114
152

I have the same question, but the only problem is that the query i have currently made shows an output almost exactly the same as the original table shown above...how do i use my query in the union sql you've shown? – JT2013 Feb 08 '12 at 13:56
I do not understand you. I have given an example using the field names shown in your example. If you have followed my example above, you cannot end up with your example. Please post the SQL you are using. – Fionnuala Feb 08 '12 at 13:59
This is the SQL that I am using...and i want to apply your union query to it: – JT2013 Feb 08 '12 at 14:01
SELECT [Authorizer Name], Sum([Q1A - CD # 1]) AS [Category 1], Sum([Q2A- CD # 2A] + [Q8A- CD # 2A] + [Q10A- CD # 2A] + [CTS A accurate- CD # 2A] + [e-correspondence A accurate- CD # 2A]) AS [Category 2], Sum([Q7A- CD # 2B] + [Q9A- CD # 2B] + [Q11A- CD # 2B] + [CTS A procedures- CD # 2B] + [e-correspondence A procedures- CD # 2B]) AS [Category 3], Sum([Q4A- CD # 3]) AS [Category 4], Sum([Q5A- CD # 4]) AS [Category 5], Sum([Q12A- CD # 5]) AS [Category 6] FROM [Review Results] WHERE [Review Results].[Authorizer Name] = 1 GROUP BY [Review Results].[Authorizer Name]; – JT2013 Feb 08 '12 at 14:02
2

When you are not sure what you are doing, imaginary data is a bad idea. Your example is not at all like your data and would require a completely different answer. What is your real data from a table, not a query, and what do you want to end up with? Just a selection of data is sufficient. – Fionnuala Feb 08 '12 at 14:05
This is the way the table currently is: Name X1 X2A X2B X2C X3A X3B X3C X4 X5 Joe 1 5 0 1 1 5 6 0 0 Bob 2 7 0 2 1 4 2 1 9 Billy 0 8 0 3 1 3 1 0 9 This is what I would like to get to: Name Joe Bob Billy X1 1 2 0 X2 (sum of X2A/X2B/X2C) 6 9 11 X3 (sum of X3A/X3B/X3C) 12 7 5 X4 0 1 0 X5 0 9 9 Thanks a bunch – JT2013 Feb 08 '12 at 14:18
is there any way to use the "count" function in the "select" statements? Ie. what if there were multiple entries for "Joe" (because the table is based on a date) and i wanted to count the number of entries for "Joe" and display in a separate column? Something like this: select count[Name] as [Number of Entries]? – JT2013 Feb 08 '12 at 17:13
Where do you want to use count? In a crosstab? – Fionnuala Feb 08 '12 at 17:15
I was afraid of that. You do not want to mess with the flat data. This is a disadvantage of posting imaginary data and outcomes. If you want a count, it should be done in queries. – Fionnuala Feb 08 '12 at 17:28
SELECT [Name], Count([Name]) FROM Table GROUP BY [Name]. You could also include something similar in the crosstab posted above as an additional column heading. You should start playing with the query design window and query wizards once you have a flat table via UNION. – Fionnuala Feb 08 '12 at 17:46
last question -> what SQL statement would you user to create a make table query for the Union query? – JT2013 Feb 08 '12 at 18:05
I would add the union query to the query design grid and choose Make Table query from the options. – Fionnuala Feb 08 '12 at 18:18

Columns to Rows in MS Access

1 Answers1

Linked