Is This Possible in Access? Convert column type data to row type?

hi,
I would like to know if this is possible.
I have a data table arranged this way:

Type   Form1  Form2  Form3  Form4
1             A         B         C          D

I would like to possibly run a query (or something) so that the table above becomes:

Type  Form
1          A
1          B
1          C
1          D
and so on...

Is that possible?

Thanks in advance.

jenica024Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
create a table with two fields  Type and Form name the table newTypes

run this codes

Sub TransposeType()
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, s, fldArr()

Set rs = CurrentDb.OpenRecordset("tblTypes")  'change the name to the actual name of table
Set rs1 = CurrentDb.OpenRecordset("NewTypes")

If rs.EOF Or rs.BOF Then
    MsgBox "no records"
    Exit Sub
End If
rs.MoveFirst
    For i = 0 To rs.Fields.Count - 1
        ReDim Preserve fldArr(i)
        fldArr(i) = rs.Fields(i).Name
    Next
Dim j
Do Until rs.EOF
    For j = 1 To UBound(fldArr)
            With rs1
                .AddNew
                !Type = rs("Type")
                !Form = rs.Fields(fldArr(j))
                .Update
               
            End With
    Next
    rs.MoveNext
Loop
End Sub
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
afaik no.  Access does not have rows like Excel does.  
0
 
Rey Obrero (Capricorn1)Commented:
you can do that using vba

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
jenica024Author Commented:
Thank you! Works fine!
I have one more question though: how can i make it not create a row with the type name? Right now when it transposes it does:

Type Form
1         1
1         A
1          B
2         2
2         A

Again, thanks for your patience and expertise. :)
0
 
Rey Obrero (Capricorn1)Commented:
post the data from the table source
0
 
jenica024Author Commented:
Sorry! Figured it out.
I have an ID field in the table source which is what was messing me up.

Thank you so much though. Your solution is PERFECT! :)
We can use this in so many of our other tables.

0
 
jenica024Author Commented:
Pure GENIUS!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.