How to reverse Transpose a table

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?
Stephen RoesnerAnalysisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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
aikimarkCommented:
you should include a key column in your output.
0
Stephen RoesnerAnalysisAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
What is the unique key (col name) to each row?
0
Stephen RoesnerAnalysisAuthor Commented:
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
aikimarkCommented:
let me restate my question...
In your original table, what column uniquely identifies each row?
0
chaauCommented:
Use UNION instead of UNION ALL and you will get rid of duplicates
0
Stephen RoesnerAnalysisAuthor Commented:
n your original table, what column uniquely identifies each row?

That would be account Number
0
chaauCommented:
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
aikimarkCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen RoesnerAnalysisAuthor Commented:
Thank you for all your hard work I appreciate it
this works great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.