?
Solved

Access Convert Columns into Rows

Posted on 2016-08-04
5
Medium Priority
?
268 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 51

Accepted Solution

by:
Gustav Brock earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

770 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