Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-11-28
7
Medium Priority
?
457 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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

610 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