Transform the fields names to rows in access

I have a access table that was created from an import of a Excel spreadsheet.  All the Dates 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
Item number |     Usage Date     | Qty
      010019     |   August 2017     |  34
      010019     |September 2017 |  10
      010019     |October 2017      |  57

No need to include the zero quantities so the result table will be simpler.
Attached is a copy of the database with this table.  Keep in mind that the way to UNPIVOT should be dynamic.  Each month we import a new spreadsheet and the column names will be different, so we need to UNPIVOT after each import of the spreadsheet.
Mohamed FathelbabAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Dale FyeCommented:
You didn't mention how the data is currently organized, so I'll assume that you have column headers like:

Item Number         August 2018        September 2017     October 2017
010019                          34                              10                         57

The way to denormalize this is:

SELECT Item_Number, "August 2017" as Usage_Date, August_2017 as Quantity
FROM yourTable
WHERE NZ(August_2017, 0) <> 0
UNION ALL
SELECT Item_Number, "September 2017" as Usage_Date, September_2017 as Quantity
FROM yourTable
WHERE NZ(September_2017, 0) <> 0
UNION ALL
SELECT Item_Number, "October 2017" as Usage_Date, October_2017 as Quantity
FROM yourTable
WHERE NZ(October_2017, 0) <> 0

Although I would recommend entering an actual date value in the [Usage_Date] column, preferring to use the first of the month, so it might look like:

SELECT Item Number, #8/1/17# as Usage_Date, August_2017 as Quantity
FROM yourTable
WHERE NZ(August_2017, 0) <> 0
UNION ALL
SELECT Item Number, #9/1/17# as Usage_Date, September_2017 as Quantity
FROM yourTable
WHERE NZ(September_2017, 0) <> 0
UNION ALL
SELECT Item_Number, #10/1/17# as Usage_Date, October_2017 as Quantity
FROM yourTable
WHERE NZ(October_2017, 0) <> 0

Note, if your column names have spaces in them (instead of the underscores I've assumed above), you will have to wrap the column names in square brackets.
0
 
aikimarkCommented:
Please post a representative sample of the imported data or post-import table data.

Please indicate what might change between runs.
0
 
Mohamed FathelbabAuthor Commented:
This works. However, modifications will be required for each month extract because the columns headings (months), or we can call them (Dates), will be changing every month. Is there anyway the query can be dynamic and adapt itself to the changes in columns heading?
I attach a sample of the extract data
MOVEX_AAA-test-.xlsx
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
aikimarkCommented:
This routine just displays the union query in the immediate window.  You would probably repackage this code into a function whose result would be invoked to instantiate a recordset variable or would append to a table or would be the data source for a make-table query.
Public Sub Q_29095444(ByVal parmTablename, ByVal parmKeyFieldsList, ByVal parmRowCriteria, Optional parmDelim As String = "^")
    '=========================================
    'NOTE: The parmRowCriteria will replace all instances of %colname% with the
    '       current data column name
    'Usage Example: Q_29095444 "Q_29095444","Item Number","[%colname%]<>0"
    '=========================================
    Dim strSQL As String
    Dim strSelectBase As String
    Dim strSelect As String
    Dim strFrom As String
    Dim vItem As Variant
    Dim td As TableDef
    Dim fld As Field
    Set td = DBEngine(0)(0).TableDefs(parmTablename)
    strSelectBase = "Select " & Replace(parmKeyFieldsList, parmDelim, ",")
    strFrom = "From " & parmTablename & " "
    
    For Each fld In td.Fields
        'ToDo: replace the InStr() function with more accurate method
        If InStr(1, parmKeyFieldsList, fld.Name, vbTextCompare) = 0 Then
            If Len(strSQL) <> 0 Then
                strSQL = strSQL & vbCrLf & "UNION ALL " & vbCrLf
            End If
            strSQL = strSQL & strSelectBase & ",""" & fld.Name & """,[" & fld.Name & "] " & _
                    vbCrLf & strFrom & " " & vbCrLf
            
            If Len(parmRowCriteria) <> 0 Then
                strSQL = strSQL & "Where " & Replace(parmRowCriteria, "%colname%", fld.Name)
            End If
        End If
    Next
    Debug.Print strSQL
End Sub

Open in new window

0
 
Mohamed FathelbabAuthor Commented:
@ aikimark, Thanks for your reply. may you please explain a bit more as I am still a beginner to Access.
this doesn't seems to be an SQL. is it possible to attach an Access file with this code, hopefully this will explain enough.
Thanks.
0
 
aikimarkCommented:
This is the immediate window contents where I invoked the routine and see the result.  The result is SQL.
NOTE: you can only process (normalize, unpivot) about 30 columns in a single query due to Access limits.
Q_29095444 "Q_29095444","Item Number","[%colname%]<>0"

Select Item Number,"April 2017 Qty Usage",[April 2017 Qty Usage] 
From Q_29095444  
Where [April 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"May 2017 Qty Usage",[May 2017 Qty Usage] 
From Q_29095444  
Where [May 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"June 2017 Qty Usage",[June 2017 Qty Usage] 
From Q_29095444  
Where [June 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"July 2017 Qty Usage",[July 2017 Qty Usage] 
From Q_29095444  
Where [July 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"August 2017 Qty Usage",[August 2017 Qty Usage] 
From Q_29095444  
Where [August 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"September 2017 Qty Usage",[September 2017 Qty Usage] 
From Q_29095444  
Where [September 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"October 2017 Qty Usage",[October 2017 Qty Usage] 
From Q_29095444  
Where [October 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"November 2017 Qty Usage",[November 2017 Qty Usage] 
From Q_29095444  
Where [November 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"December 2017 Qty Usage",[December 2017 Qty Usage] 
From Q_29095444  
Where [December 2017 Qty Usage]<>0
UNION ALL 
Select Item Number,"January 2018 Qty Usage",[January 2018 Qty Usage] 
From Q_29095444  
Where [January 2018 Qty Usage]<>0
UNION ALL 
Select Item Number,"February 2018 Qty Usage",[February 2018 Qty Usage] 
From Q_29095444  
Where [February 2018 Qty Usage]<>0
UNION ALL 
Select Item Number,"March 2018 Qty Usage",[March 2018 Qty Usage] 
From Q_29095444  
Where [March 2018 Qty Usage]<>0

Open in new window

0
 
Dale FyeCommented:
If the data will change each month, you could dynamically construct the union query by looping through the columns in the table, building the query one "column" at a time.  This is untested:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim fld as DAO.Field
Dim intLoop as integer
Dim strSQL as string

set db = currentdb

'open a recordset that contains no records but will have columnheaders
set rs = db.openrecordset("SELECT * FROM yourTable WHERE 0 = 1")

'loop through the fields, starting at the 2nd field
for intLoop = 1 to rs.fields.count - 1
    if len(strSQL) > 0 then strSQL = strSQL & " UNION ALL "
    strSQL = strSQL _
                 & "SELECT Item_Number, '" & fields(intLoop).Name & "' as Usage_Date, [" & fields(intLoop).Name & "] as Quantity " _
                 & "FROM yourTable " _
                 & "WHERE NZ([" & fields(intLoop).Name & "], 0) <> 0"
Next

debug.print strSQL

Open in new window

In this sample, I simply print the final SQL string, but you might execute that query, or save that SQL as the source of another query.
0
 
Mohamed FathelbabAuthor Commented:
All, Thanks a lot . I really appreciate your support. please find the function I used for my database in attached access file. hope this can help for any similar case.
column-to-row.accdb
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Dale FyeCommented:
glad we could help Mohamed
0
 
Dale FyeCommented:
I think the OP should give credit to both Dale (me) and aikimark for providing guidance on how to accomplish this task.
0
 
Mohamed FathelbabAuthor Commented:
File is attached with the complete function to achieve this task for any similar cases.
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.