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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,982
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
63,982
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
63,982
Office Version
  1. 365
Platform
  1. Windows
Your welcome! Glad it worked out for you!:cool:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,443
Members
431,879
Latest member
KiwDaWabbit

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
Top