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?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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:
Upvote 0
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!!
 
Upvote 0
Your welcome! Glad it worked out for you!:cool:
 
Upvote 0

Forum statistics

Threads
1,217,317
Messages
6,135,833
Members
449,965
Latest member
Ckl43

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top