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

x
?
Solved

Access Convert Columns into Rows

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

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 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