Solved

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

Posted on 2007-11-28
7
408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 65

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 500 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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