Transform m:n-matix into n:m-matrix

[barbarossaii]

Hi,

is there a way of transforming a m:n-matrix (a table in my db) into an n:m-matrix (result of a query) ?
The query should diplay its elements Q_ji pulled from the table elements T_ij in a way that Q_ji equals T_ij (with i=1..m and j=1:n). For a better understanding of the query, the names of the 'lines' and 'columns' of the table should be transformed for display in the query, too.

TIA,
Barbarossa II

PS: I'm sorry for my poor englisch in my question (it"s not my native tongue).

 

[jjturner]

Re-consider your table design

Normally, if you're attempting to TRANSFORM your table data you would use a Crosstab Query. Converting the rows from 1 field in your table to column headings in a Crosstab Query is a common method of manipulating table data.

Conversely, UNION-ing a single row of table field values into a single column of Query results goes against principles of relational-database design. Unless your table fields are all of the same datatype and all representative of the same classification, you will be mixing types of data in your result set field(s).

If you DO have identical datatypes across each field definition in your table, then this begs the question of how your table is designed. Relational DB tables aren't meant to be matrices. Rather, you should re-design your table so that in applying a Crosstab Query, your query result will be identical to the records in the table as it currently exists.

HTH,
John

 

[barbarossaii]

made question more conrete (hope so!)

Hi John & everyone willing to help me,

I suppose I wasn't able to decribe the problem, I have, properly in my last post; so I add a new try:


I have got a table like this:


Identifier Col1 Col2 Col3

A A1 A2 A1
B B1 B2 B3
C C1 C2 C3


and I'd like to display it via a query like this:

Identifier <Headline1> <Headline2>
A Col1 A1
A Col2 A2
A Col3 A3
B Col1 B1
B Col2 B2
B Col3 B3
C Col1 C1
C Col2 C2
C Col3 C3



I need the table in both ways and it is much easier to start with the (first) table as depicted above.

How can I do the ''''''tansformation'''''' ?

Again TIA,

Barbarossa II

 

[Jon K]

Try this Union query (it is easier to type it in the SQL View of a new query):-

SELECT Identifier, "Col1" as [Headline1], [Col1] as [Headline2] FROM [TableName]
UNION
SELECT Identifier, "Col2", [Col2] FROM [TableName]
UNION
SELECT Identifier, "Col3", [Col3] FROM [TableName];

 

[barbarossaii]

Problem solved!

You solved the problem !


Thanks for your help,

Barbarossa II