Transpose query (1 Viewer)

[mrtn]

Hi all

I have spent last hour trying to figure out how to transpose my table and giving up :banghead:

My data looks as follows

Client ref  Bonds   Equities
Client1       41%       21%
Client2       10%       80%

I need it to look as below

Client ref  Asset Class   Weight
Client1       Bonds           41%
Client1       Equities         21%
Client2       Bonds           10%
Client2       Equities         80%

Any help would be much appreciated.

 

[sneuberg]

You could do it with two append queries. In the first the Client Ref would append to Client Ref, Asset Class would just be an expression like Asset Class: "Bonds" and Weight would be appended from Bonds. In the second the Client Ref would append to Client Ref, Asset Class the expression Asset Class: "Equities" , and the Weight would be append from Equities.

If you post a copy of your database with the table I'll set them up for you.

 

[sneuberg]

I reread your post and now I'm not sure if you want a new table. If not just make two select queries as I described and then put them together with a union query.

 

[mrtn]

thanks for getting back so quickly.

I have created only two columns in above example (Bonds, equities) just to show what I need. In a real life scenario I have got a table with 16 columns and whilst creating 16 queries would work I am wondering whether there is better solution?

 

[plog]

In a real life scenario I have got a table with 16 columns...

The initial post made it seem like your data was improperly structured, and that confirms it. The result of what you want to achieve is how your tables should be structured, not as they are now.

You really need to fix your table structure.

 

[mrtn]

The initial post made it seem like your data was improperly structured, and that confirms it. The result of what you want to achieve is how your tables should be structured, not as they are now.

You really need to fix your table structure.

Unfortunately the data comes from excel export from another in-house system and I can't really do anything about it.

 

[Minty]

Actually using excel you can reverse engineer this data back into a normalised list.

http://blog.contextures.com/archives/2011/07/20/normalize-data-for-excel-pivot-table/

Describes the process and it does work - I used it on a old fashioned prices list that had about 60 Columns of products and 300 rows of customer specific pricing

 

[plog]

Unfortunately the data comes from excel export from another in-house system and I can't really do anything about it.

Did they finally pass that bill about not changing Excel formatted data into properly structured tables? Damn Obama.

 

[mrtn]

Did they finally pass that bill about not changing Excel formatted data into properly structured tables? Damn Obama.

Obviously I am being thick here... Can you explain how it's done properly?

 

[plog]

You build a proper structure (e.g normalize your data). Then you build a process to move your exported data into that structure. That usually involves importing your data into a temporary table (tmp_Data) then building APPEND queries to move it from tmp_Data to the proper table/fields each piece needs to go into.

For the example data you initially posted, you could do it with 2 APPEND queries--1 to move Bond data and 1 to move Equity data. In that instance, both queries would APPEND data to the same table. You would then have your data exactly like you wanted and could run further queries on it (Average Bond allocation, clients with <100% allocations, etc.).

I'm sure your process would be more complicated than that, but that's the broad strokes. Then once you have your entire import process built, you could build a macro which runs each part of it one after another so all you have to do is run a macro and data goes where its suppose to.

 

[sneuberg]

Obviously I am being thick here... Can you explain how it's done properly?

If this is in a spreadsheet then, I think Minty's post is your answer. If you click on the link he provide there's a link to a video that explains the process. It really awesome.