Solved

Access Convert Columns into Rows

Posted on 2016-08-04
5
48 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

705 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

12 Experts available now in Live!

Get 1:1 Help Now