Converting matrix to column in access 2010

[peteramullen]

Hello all,

I'm trying to convert a matrix within a table (m x n) to a single column. So basically

1 2 3


4 5 6
7 8 9

to

1
2
3
4
5
6
7
8
9

I would do this in excel, but the problem is I have already over 1 million rows, and I'm converting 48 columns into one (too much for excel). Is there a way to do this via a macro or query?

The other catch is that I want to repeat Column A (which has the username) with each row of data, for example

Joe 1 2 3
Sam 4 5 6
Jeff 7 8 9

to

Joe 1
Joe 2
Joe 3
Sam 4
Sam 5
Sam 6
Jeff 7
Jeff 8
Jeff 9

any ideas or thoughts would be amazingly appreciated! Thanks!

[pbaldy]

How about a UNION query?

SELECT ColumnA, ColumnB
FROM TableName
UNION ALL
SELECT ColumnA, ColumnC
FROM TableName
UNION ALL
...

[peteramullen]

Thanks pbaldy, that has already helped a lot. A follow up question has spawned though:

How can I get ColumnB, Column C, etc names to be associated with those rows?


Basically I won't be able to tell which column they came from unless they are somehow designated to have come from that column..

Any help there? Sorry am new to access..

[pbaldy]

Try

SELECT ColumnA, ColumnB, "ColumnB" As Source
FROM TableName
UNION ALL
SELECT ColumnA, ColumnC, "ColumnC" As Source
FROM TableName
UNION ALL
...

[peteramullen]

Thanks again, worked great.

I realized that there are some values that need to be summed -- the Column B values, Column C values etc, within each Column A category.

How do I go about subtotaling?

I currently have been trying to query a total off of this initial query we've been discussing above. I'd be great if I could embed it in the original SQL code though. Thanks for all the help!!!