Transform the fields names to rows in access

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.

Please post a representative sample of the imported data or post-import table data.

Please indicate what might change between runs.

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

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

Select allOpen in new window

@ 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.

glad we could help Mohamed

I think the OP should give credit to both Dale (me) and aikimark for providing guidance on how to accomplish this task.

File is attached with the complete function to achieve this task for any similar cases.
Thanks.