Is there an easy way to un-crosstab query a table? (normalize)

Question

I have just inherited a new database to maintain. But one of the tables is, well, not great. I could write a complex query to fix this problem, but it would be long and tedious. I am wondering if there is an easier way to it?

The table looks like this :

Name	Data point	Date1	date 2	date 3	date 4	...	dateN
alpha	yes	date	date	date	date	...	date
beta	no	date	date	date	date	...	date

Thing is , what I need it to look like is this :

Name	Data point	DateCat	Date
alpha	yes	date1	date
beta	no	date2	date
alpha	yes	date3	date
beta	no	date4	date
alpha	yes	date2	date
beta	no	date1	date

Now a crosstab would allow me to go from what I want to what I have, is there an easy way to do the reverse, or is this a "Get your hands dirty and do it manually" situation? I am considering exporting the table, throwing it in to pandas and manipulating it that way, because I know I can do it there. Hoping Access has something built in for this?

This is a "pivot". If you actually clearly described the sort of transformation you are talking about you would find it googling. And how else could you google it? This is a faq. Please before considering posting read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. — philipxy, Aug 16 '21 at 19:54
So what you're saying is, that I have to do it the long way, as pivot tables are no longer available in the version of Access I am using. I was hoping they had something replace it that I hadn't found. — Christopher J. Joubert, Aug 16 '21 at 20:02
Research pivoting (and simple statement of the transformation) in SQL & in Access. Depends on the dialect/DBMS/product. I didn't specifically say "pivot table". Words get used for multiple, variant & different things. — philipxy, Aug 16 '21 at 20:04
See also here for UNPIVOT: https://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access — Andre, Aug 17 '21 at 07:16
Thanks Andre, I do know how to do it manually, I was just hoping that like the Crosstab Query, Access might have a built in function to do it, I was looking at Applecore's answer and it might be easier for me to do it that way, or via export than SQL, as the table I need to unpivot is much larger than my example, 54 columns to unpivot exactly. Thank you again though. — Christopher J. Joubert, Aug 17 '21 at 12:11
Pivot and Crosstab are essentially the same transform. Likewise, Uncrosstab and unpivot are essentially the same. — Walter Mitty, Aug 17 '21 at 13:14
https://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access or https://stackoverflow.com/questions/47798545/ms-access-using-variables-to-unpivot-100-columns This appears to have been asked/answered before — xQbert, Feb 07 '23 at 16:36

score 1 · Accepted Answer · edited Feb 07 '23 at 16:28

Whilst Access doesn't have a single built-in function to do what you want, it is relatively easy to create something using VBA that loops the fields in the first table to output the data as required. Something like:

Sub sUnXTabData()
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsSteer As DAO.Recordset
    Dim rsData As DAO.Recordset
    Dim intCount As Integer
    Dim intLoop1 As Integer
    Set db = CurrentDb
    Set rsSteer = db.OpenRecordset("SELECT * FROM tblXTab;")
    If Not (rsSteer.BOF And rsSteer.EOF) Then
        db.Execute "DELETE * FROM tblNotXTab;"
        Set rsData = db.OpenRecordset("SELECT * FROM tblNotXTab WHERE 1=2;")
        intCount = rsSteer.Fields.Count - 1
        Do
            For intLoop1 = 2 To intCount
                rsData.AddNew
                rsData("Name") = rsSteer("Name")
                rsData("Datapoint") = rsSteer("Datapoint")
                rsData("DateCat") = rsSteer.Fields(intLoop1).name
                rsData("Date") = rsSteer.Fields(intLoop1)
                rsData.Update
            Next intLoop1
            rsSteer.MoveNext
        Loop Until rsSteer.EOF
    End If
sExit:
    On Error Resume Next
    rsSteer.Close
    rsData.Close
    Set rsSteer = Nothing
    Set rsData = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sUnXTabData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Please note that Name and Date are reserved words in Access, so you should change them.

Is there an easy way to un-crosstab query a table? (normalize)

1 Answers1