Solved

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

Posted on 2007-11-28
7
398 Views
Last Modified: 2013-11-27
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.

0
Comment
Question by:jenica024
  • 3
  • 3
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
afaik no.  Access does not have rows like Excel does.  
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can do that using vba

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jenica024
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
post the data from the table source
0
 

Author Comment

by:jenica024
Comment Utility
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
 

Author Closing Comment

by:jenica024
Comment Utility
Pure GENIUS!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now