?
Solved

How to reverse Transpose a table

Posted on 2014-08-19
11
Medium Priority
?
188 Views
Last Modified: 2014-08-21
I have a table of 33 fields which can have multiple records.
I need to grab 8 of these fields which no matter how many records will all have the same data.
I need to use this for a header.
So in fact I could grab just one record.
But I need to have them in top to bottom order.
So that:
Client Name    Account Number        Client Name      Client ID        Renewal Date        Term Date          etc etc etc
data                   data                              data                   data                       data                   data    

Looks like this:
Client Name               data  
Account Number      data  
Client Name               data  
Client ID                     data  
Renewal Date            data  

Can this been done in a query?
0
Comment
Question by:Stephen Roesner
  • 4
  • 4
  • 3
11 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40271503
You can use UNION ALL to unpivot the table:
SELECT 'Client Name' as [source], CStr([Client Name]) AS [value], A, B, C
FROM tblUnpivotSource
UNION ALL
SELECT 'Account Number' as [source], CStr([Account Number]) AS [value], A, B, C
FROM tblUnpivotSource
UNION ALL
SELECT 'Client ID' as [source], CStr([Client ID]) AS [value], A, B, C
FROM tblUnpivotSource;

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 40272368
you should include a key column in your output.
0
 

Author Comment

by:Stephen Roesner
ID: 40274152
OK so far so good I have created the union all query - ( I didn't understand the A_B_C) pretty cool since I have never done one in all my time in access.
I tried to do some research on the net but its very slim on union all's so I do see the value of doing a key column
since it looks like it sorts fields alpha which I don't want. And I tried to do a group by to remove all the dup records
but I could figure it out.Here is the query I wrote:

How do I add a key column and how would I do a group by?
(I tried Group By [source],[value]; at the very end but it failed)

SELECT 'Client Name' as [source], CStr([Client Name]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Account Number' as [source], CStr([Account Number]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Client ID' as [source], CStr([Client ID]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Original Effective Date' as [source], CStr([Original Effective Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Renewal Date' as [source], CStr([Renewal Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Term Date' as [source], CStr([Term Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Transaction Type' as [source], CStr([Transaction Type]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Transaction Date' as [source], CStr([Transaction Date]) AS [value]
FROM Structure_History
UNION ALL SELECT 'Processed By' as [source], CStr([Processed By]) AS [value]
FROM Structure_History;
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 46

Expert Comment

by:aikimark
ID: 40274316
What is the unique key (col name) to each row?
0
 

Author Comment

by:Stephen Roesner
ID: 40274564
that would be the source column but what could be an issue is when i do the union all i get dup records unless the (group by) will handle that within the query so for a visual when i do the union all query as is, it looks like this with both source and values being dups or maybe at times trips quad etc etc:
[Source]                    [value]
Client Name               data  
Client Name               data  
Account Number      data  
Account Number      data  
Client ID                     data  
Client ID                     data  
Renewal Date            data  
Renewal Date            data
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40274658
let me restate my question...
In your original table, what column uniquely identifies each row?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40274700
Use UNION instead of UNION ALL and you will get rid of duplicates
0
 

Author Comment

by:Stephen Roesner
ID: 40275002
n your original table, what column uniquely identifies each row?

That would be account Number
0
 
LVL 25

Expert Comment

by:chaau
ID: 40275130
The "A, B, C" in my original answer are additional columns you want to be included in the select statement. You may wish to include at least one extra column to separate the groups.
If you wish to use group by you need to modify the query like this:
SELECT [source], [value] FROM (
SELECT 'Client Name' as [source], CStr([Client Name]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Account Number' as [source], CStr([Account Number]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Client ID' as [source], CStr([Client ID]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Original Effective Date' as [source], CStr([Original Effective Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Renewal Date' as [source], CStr([Renewal Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Term Date' as [source], CStr([Term Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Transaction Type' as [source], CStr([Transaction Type]) AS [value]
FROM Structure_History
UNION ALL
SELECT 'Transaction Date' as [source], CStr([Transaction Date]) AS [value]
FROM Structure_History
UNION ALL SELECT 'Processed By' as [source], CStr([Processed By]) AS [value]
FROM Structure_History) as Q
GROUP BY [source], [value]

Open in new window

0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40275300
You would add the account number key like this:
SELECT [Account Number], 'Client Name' as [source], CStr([Client Name]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Account Number' as [source], CStr([Account Number]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Client ID' as [source], CStr([Client ID]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Original Effective Date' as [source], CStr([Original Effective Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Renewal Date' as [source], CStr([Renewal Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Term Date' as [source], CStr([Term Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Transaction Type' as [source], CStr([Transaction Type]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Transaction Date' as [source], CStr([Transaction Date]) AS [value]
FROM Structure_History
UNION ALL 
SELECT [Account Number], 'Processed By' as [source], CStr([Processed By]) AS [value]
FROM Structure_History;

Open in new window

Of course, you don't really need the account number row, since all rows will have an account number key.  Thus, I recommend this version
SELECT [Account Number], 'Client Name' as [source], CStr([Client Name]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Client ID' as [source], CStr([Client ID]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Original Effective Date' as [source], CStr([Original Effective Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Renewal Date' as [source], CStr([Renewal Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Term Date' as [source], CStr([Term Date]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Transaction Type' as [source], CStr([Transaction Type]) AS [value]
FROM Structure_History
UNION ALL
SELECT [Account Number], 'Transaction Date' as [source], CStr([Transaction Date]) AS [value]
FROM Structure_History
UNION ALL 
SELECT [Account Number], 'Processed By' as [source], CStr([Processed By]) AS [value]
FROM Structure_History;

Open in new window

0
 

Author Closing Comment

by:Stephen Roesner
ID: 40277454
Thank you for all your hard work I appreciate it
this works great
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

864 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