Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-11-28
7
Medium Priority
?
522 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 66

Expert Comment

by:Jim Horn
ID: 20368350
afaik no.  Access does not have rows like Excel does.  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20368422
you can do that using vba

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 20368499
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:jenica024
ID: 20368675
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20368708
post the data from the table source
0
 

Author Comment

by:jenica024
ID: 20368755
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
ID: 31411513
Pure GENIUS!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

571 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