Column values to rows

[sanniep]

Hello,

I have a table like this:

ID Value1 Value2 Value3 Value4
124422 ABC HGV HGB
213441 HGB KJU
324523 HJU YGH JHU JHY
345523 YGF

There are about 4000 rows with unique ID's and a maximum of 40 value columns.

I want the result to be like this:

ID Value
124422 ABC
124422 HGV
124422 HGB
213441 HGB
213441 KJU
324523 HJU
324523 YGH
324523 JHU
324523 JHY
345523 YGF

Can you please help me with the right code, sql or function to do this?

Hope to hear from you!
Thanks, Sander

[ADezii]

This can be done relatively easily with Nested Recordset Loops. I'll hang back and see if anyone comes up with a better, alternative approach before I post mine. BTW, can Values in Fields be jumped as in?

124422  ABC  HGV  NULL  NULL  HGB  NULL  YUT

[Rabbit]

You can use a series of unions to get the result you want.

[ADezii]

You can use a series of unions to get the result you want.

Wouldn't you have to know the number of Columns as well as their Names ahead of time for the UNIONs to work?

[Rabbit]

Yes, but I believe that is the case here.

[sanniep]

In this case there will be 40 columns. But for each ID, I don't know how many columns have a value. It could be 0, it could be 40.

If I use Unions, how will I have the ID copied to the necessary number of rows?

[Rabbit]

SELECT id, field1
FROM someTable
WHERE field1 IS NOT NULL

UNION ALL

SELECT id, field2
FROM someTable
WHERE field2 IS NOT NULL

And so on.

[ADezii]

The following Code will give you exactly the results that you are looking for, assuming your Table Name is Table1. It makes no difference how many Fields are in your Table, and it also makes no difference how many of those Fields contain NULL Values for each individual ID. Simply:

1. Create a Table named tblResults with 2 Fields, named [ID]{LONG} and [VALUE_R]{TEXT}.
2. Execute the below listed Code.
3. The Linear Data will be converted to a Tabular Format.
4. Should you have any questions at all, simply ask since I left out Comments for the sake of brevity.

Dim MyDB As DAO.Database
Dim rs As DAO.Recordset
Dim intNumOfFields As Integer
Dim intCtr As Integer

Set MyDB = CurrentDb
Set rs = MyDB.OpenRecordset("Table1", dbOpenSnapshot, dbOpenForwardOnly)

CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError

With rs
 intNumOfFields = .Fields.Count
  Do While Not .EOF
    For intCtr = 1 To (intNumOfFields - 1)
      If Not IsNull(.Fields(intCtr)) Then
        CurrentDb.Execute "INSERT INTO tblResults ([ID], [VALUE_R]) VALUES(" & ![ID] & ",'" & _
                           .Fields(intCtr) & "')", dbFailOnError
        'Debug.Print ![ID] & " | " & .Fields(intCtr)
      End If
    Next
    .MoveNext
  Loop
End With

rs.Close
Set rs = Nothing