Modified Trasnpose - Help

[stevieb]

Hi,

I have data stored in Table1 as such (Field names on row 1):

Id - Type1 - Type2
123 - 0 - 50
456 - 10 - 100
789 - 50 - 0

And I would like to output it as such (Field names on row 1):

Id - Type - Amount
123 - Type1 - 50
456 - Type1 - 10
456 - Type2 - 100
789 - Type1 - 50

I will have more the 20 Types in my actual table, so I can't just make
compounded queries. Any help is appreciated, thanks!

 

[Douglas J. Steele]

SELECT Id, "Type1" As Type, Type1 As Amount
FROM MyTable
UNION
SELECT Id, "Type2", Type2
FROM MyTable
UNION
SELECT Id, "Type3", Type3
FROM MyTable
UNION
....
UNION
SELECT Id, "Type20", Type20
FROM MyTable

 

[stevieb]

Hello,

One modification to the table below...instead of Type1, Type2, Typen, I
really want to show it as 1,2,3..n.

For some reason when I do the compunding queries, it does not like a number
as a Field name.

 

[stevieb]

NVM - just figured it out..I had to put brackets in my unioned queries.. ie.
[1] as Type.

 

[John W. Vinson]

Hello,

One modification to the table below...instead of Type1, Type2, Typen, I
really want to show it as 1,2,3..n.

For some reason when I do the compunding queries, it does not like a number
as a Field name.

Neither do I as far as that goes, and your example suggested that the results
were all going into one field! Do you mean that you had data in fields named
[1], [2] etc. to start with, or that you want multiple *fieldnames* (not field
values) in the result?