How to get column header as row data

[mrchonginhk]

Table1 is 4 column table like this

ID, A, B, C
========
1, X, Y, Z
2, D, E, F
3, J, K, L
etc
etc

I want a query producing this result

ID,Item, Value
===========
1, A, Y
1, B, Z
1,C, Z
2, A, D
2, B, E
2, C, F
3, A, J
3, B,K,
3, C, L

How to do this ?

 

[sheetspread]

reverse pivot table

https://www.youtube.com/watch?v=xmqTN0X-AgY

 

[strive4peace]

I must ask what kind of data you are talking about? Seems to me that it is not properly structured for Access. It is much easier to pivot data to get it INTO the format you seem to be storing it in than the other way.

You can also use Excel, as suggested by sheetspread, to transpose the data. Then put it back into Access.

In Excel, You can also:
.1. select the data
.2. copy
.3. paste special -- transpose

to get something like this:

ID	1	2	3
A	X	D	J
B	Y	E	K
C	Z	F	L

<colgroup><col span="4"></colgroup><tbody>
</tbody>

then, change header add another column to the front for the ID and use copy and paste to get this:

ID	fld	val
1	A	X
	B	Y
	C	Z
2		D
		E
		F
3		J
		K
		L

<colgroup><col span="3"></colgroup><tbody>
</tbody>

To get A, B, C to repeat:
select the cells and double-click the AutoFill handle (or drag the values down from the AutoFill handle in the lower right corner)

to propagate the values down in column A:

select the cells in A

press F5 (Goto) and choose Special --> blanks

all the blank cells will be selected

in the active cell (A3), enter
=A2
and press Ctrl-Enter

Then you can put the normalized data back into Access ~

 

[sheetspread]

In Access VBA you could loop a union query, but the Excel solutions above are much faster ad hoc.