MS Access VB to convert Columns to new rows.

[djredden73]

So I have a large dataset with very minimal columns.
Something like this:

Primary_KEY	ITEM1	ITEM2	ITEM3	STATUS
123456ABC	FOOD	DRINK	SILVERWARE	PAID

<TBODY>
</TBODY>

I would like to have that turn into:

Primary_KEY	ITEMS	STATUS
123456ABC	FOOD	PAID
123456ABC	DRINK	PAID
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?

 

[Joe4]

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]

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.

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.

 

[djredden73]

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]

Your welcome! Glad it worked out for you!