Converting multiple columns into one row

[WuJu]

Hello VBA scripters,

I need help on converting multiple columns into one row (actually two rows).
I have a Access table with 26 columns UnitID, Date, 1, 2, 3, 4,.......,24.
(data in column "1" means KWh used in hour 1, and samething appplies to each hour until hour 24)

I want to convert this table to four columns (rows expands to 24 times larger since columns of hours changes to rows)

UnitID, Date, Hour, KWh_used


Table before converting is below

UnitID date 1 2 3 ............ 24
777 1/1/2010 73 95 68 ............ 95
777 1/2/2010 59 87 91 ........... 102
.........

888 1/1/2010 211 222 189 ........... 235
.........

999 1/1/2010 153 133 155 ........... 143
........

and, a table structure I want to see after conversion is below

unitID Date Hour KWh
777 1/1/2010 1 73
777 1/1/2010 2 95
777 1/1/2010 3 68
........
777 1/1/2010 24 95
777 1/2/2010 1 59
777 1/2/2010 2 87
......


888 1/1/2010 1 211
888 1/1/2010 2 222
.......



Could you please help me using Visual Basic (in Access 2007) to convert multiple columns of KWh used in each hour into two columns, Hour and KWh used ?

Regards,

WUJU

 

[pbaldy]

Try a UNION query:

SELECT UnitID, [date], "1", [1]
FROM TableName
UNION ALL
SELECT UnitID, [date], "2", [2]
FROM TableName
UNION ALL
...

 

[WuJu]

Thanks Paul for your quick respond.
I will try your UNION query. Final product has to be in table format, not query.
So can this output from query be a table (with index which made of UnitID, date, and Hour combine)?

By the way, I may need to do this in VBA in MS Access (with someone's request), and do you know how to script VB to make this works?
If anyone knows, please help me out.

Regards,

WUJU

 

[Brianwarnock]

I guess that the 24 queries could be 24 append to table queries.

Why would anybody specifically want it in VBA, I'm sure if that was the better way to do it Paul would have said so.

Brian

 

[pbaldy]

Methinks Brian gives me more credit than I deserve.

I do think the query is the easier solution, especially since I assume it's a one-time thing. You can use the UNION query as the base of an append or make table query.

 

[WuJu]

I was able to generate query output that has the order of columns: unitID, Date, Hour, KWh.
Could you let me know how to make this query output to a table form (with the index of unitID,date,Hour combination)?
Brian mentioned that 24 queries could be 24 append to table queries. But this is not appending of existing table. It is rather converting a table that I have run a query into a new table of its own and adding a index of unitID,date,Hour combination.

I can't seem to figure out how to do it.

By the way, we were prefer using VBA in Access for converting since proccessing time may be faster since we are dealing with more than 5 million data.

Regardless of pros and cons of two ways, I want to try both in query and VBA as learning experiance.

I appreciate with anyone's suggestion and feedback.

Regards,

WuJu