Solved

Access Convert Columns into Rows

Posted on 2016-08-04
5
106 Views
Last Modified: 2016-08-04
I have a access table that was created from an import of a Excel spreadsheet.  All the DueDates for the part are represented as column names.  I need to essentially "UNPIVOT" this table and put the column names as rows.  The desired output would look like this
ItemNumber |DueDate | Qty
A |07-11-16 |1
A | 07-18-16 | 1

Attached is a copy of the database with this table.  Keep in mind that the way to UNPIVOT should be dynamic.  Each week we import a new spreadsheet and the column names will be different, so we need to UNPIVOT after each import of the spreadsheet.
unpivot.accdb
0
Comment
Question by:maverick0728
[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
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 41743089
Dumb question: can you not copy-and-paste-special-transpose the spreadsheet before import?
0
 

Author Comment

by:maverick0728
ID: 41743109
You can use PowerQuery in Excel to UNPIVOT, but it's too many steps for the different users to perform consistently.  The thought was to move it into Access so with a button and some VBA the columns can be unpivoted or transformed into rows.
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41743166
You can use this function:
Public Function TransformImport()

    Dim db  As DAO.Database
    Dim rss As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim fld As Integer
    
    Set db = CurrentDb
    Set rss = db.OpenRecordset("tblForecastImport")
    Set rst = db.OpenRecordset("Select Top 1 * From tblForecast")
    
    While rss.EOF = False
        For fld = 2 To rss.Fields.Count - 1
            rst.AddNew
                rst!ItemNumber.Value = rss!ID.Value
                rst!DueDate.Value = DateValue(rss.Fields(fld).Name)
                rst!Qty.Value = rss.Fields(fld).Value
            rst.Update
        Next
        rss.MoveNext
    Wend
    
    rst.Close
    rss.Close
    
    Set rst = Nothing
    Set rss = Nothing
    Set db = Nothing
    
End Function

Open in new window

as shown in the attached demo.

/gustav
unpivot.accdb
1
 
LVL 26

Expert Comment

by:Nick67
ID: 41743177
So you want this result?
unpivot.accdb
0
 

Author Closing Comment

by:maverick0728
ID: 41743207
it works thanks.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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