Solved

Access Convert Columns into Rows

Posted on 2016-08-04
5
61 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
  • 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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

863 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

26 Experts available now in Live!

Get 1:1 Help Now