MS Access VB to convert Columns to new rows.

djredden73

New Member
Joined
Aug 10, 2012
Messages
29
So I have a large dataset with very minimal columns.
Something like this:

Primary_KEYITEM1ITEM2ITEM3STATUS
123456ABC FOOD DRINKSILVERWAREPAID

<TBODY>
</TBODY>


I would like to have that turn into:


Primary_KEYITEMSSTATUS
123456ABC FOODPAID
123456ABC DRINKPAID
123456ABC SILVERWARE

<TBODY>
</TBODY>

I have almost 8,000,000 rows of data, so I know this is going to triple my records but wanted to know if it can be done in VB in Access?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
What isn't the Status column set as "Paid" for Silverware, when it is for Food and Drink?
I am just trying to understand the logic you are trying to apply here.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
If that was a mistake, and they should all say "Paid" (I really don't know how it could be interpretted otherwise), you don't need VBA at all. Just a simple Union Query should do the trick.

Just substitute "MyTable" with the actual name of your table in the SQL code below.
Code:
SELECT MyTable.Primary_KEY, [MyTable]![ITEM1] AS ITEMS, MyTable.STATUS
FROM MyTable
UNION
SELECT MyTable.Primary_KEY, [MyTable]![ITEM2] AS ITEMS, MyTable.STATUS
FROM MyTable
UNION
SELECT MyTable.Primary_KEY, [MyTable]![ITEM3] AS ITEMS, MyTable.STATUS
FROM MyTable;
Note that this Union Queries are not updateable. If you need to create a table so you can update the data, just create a Make Table Query from this Union Query.
 
Last edited:

djredden73

New Member
Joined
Aug 10, 2012
Messages
29
You are correct; I just failed to type in "Paid" in the one field when I was posting.

The method you mentioned is exactly correct. I did a Union on all into new table and it worked like a charm.

Thank you!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
Your welcome! Glad it worked out for you!:cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,362
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top