Converting Column Data to Row Data

[littleclayjar]

I have an Access table structured in the following way:

SupplierID Packaging1 Packaging2
1 YES YES
2 YES NO
3 NO YES

I need to restructure it so it looks like this:
SupplierID PackagingType Value
1 Packaging1 YES
1 Packaging2 YES
2 Packaging1 YES
2 Packaging2 NO
3 Packaging1 NO
3 Packaging2 YES

Is there a name for this process and how would i go about writing VBA code to restructure it?

Thanks for the help!

 

[Zion7]

Very quickly, of the top of my head...


Rec As New ADODB.Recordset, SQL AS String, x as integer

conn as New ADODB.Connection

Set Conn = CurrentProject.Connection

rec.open "tblPackage", conn, adOpenForwardOnly, adReadOnly

Do Until rec.EOF
For x = 1 To 2
SQL = "INSERT INTO NewTAble(SupplierID, PackagingType, Value)" & _
"VALUES(" & rec(0) & ",'" & rec.Fields.Name & _
"','" & rec(x) & "')"
conn.Execute SQL,,adExecuteNoRecords
Next x
rec.MoveNext
Loop

Conn.Close: Set Con = Nothing
rec.Close: set rec = Nothing


...something like that, check the logic if correct.

 

[MichaelRed]

The sample provided by Zion7 looks ok for the very few fields and not many records.

There is at least one procedure posted here in (Tek-Tips) which sort of emulated the cross tab / piviot function. I believe its' name included "Piviot". It might be a bit better for use on larger data sets.



MichaelRed

 

[SkipVought]

Hi,

If you get the data into Excel, you can use this to quickly normalize your data. faq68-5287

Skip,

_[red][/red]

 

[CautionMP]

littleclayjar,
Have you considered using a UNION query instead of VBA?

[tt]SELECT SupplierID, "Packaging1" AS PackagingType, Packaging1 AS [Value]
FROM YourTable;
UNION SELECT SupplierID, "Packaging2" AS PackagingType, Packaging2 AS [Value]
FROM YourTable;[/tt]

This could easily be used as the source for a Make Table Query.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Zion7]

Actually, small oversight (in case you decide to use this)...

Do Until rec.EOF
For x = 1 To 2
SQL = "INSERT INTO NewTAble(SupplierID, PackagingType, Value)" & _
"VALUES(" & rec(0) & ",'" & rec(x).Name & _
"','" & rec(x) & "')"
conn.Execute SQL,,adExecuteNoRecords
Next x
rec.MoveNext
Loop