locked
Change column data to row data RRS feed

  • Question

  • I have a table that has the following columns

    Part, Description, Date1, Date2, Date3, ...

    Data under the date Headers is Quantity

    I want to chnage the table to columns as follows

    Part, Description, Date, Quantity

    So for each column date header, I want t0 include that date as part of the row and the quantity also.

    data currently looks like 

    Part, Description, 06-18-2020, 06-19-2020, 06-20-2020

    1       Part 1                   10                 10               10

    2      Part 2                    20                 20               20

    I want the data to look like

    Part, Description, Date,            Quantity

    1     Part 1         06-18-2020          10

    1     Part 1         06-19-2020          10

    1     Part 1         06-20-2020          10

    2     Part 2         06-18-2020          20

    2     Part 2         06-19-2020          20

    2     Part 2         06-20-2020          20

    Is this possible?

    Thursday, July 16, 2020 3:29 PM

All replies

  • First, if you keep adding new fields such as Date1, Date2, Date3, ...etc., then obviously you are going to run up against the ACCESS table field number limit of 255 fields eventually. That's because you are attempting to use ACCESS as a spreadsheet. 

    You should only have one Date field in the table. Then each time you wish to change the Part, Description, and Quantity Date, you append (add) a new record in the table with a new Date in the Date field. Then you can achieve your goal of listing and sorting by Date AND ACCESS won't completely crash on you when you get near the 255 field limit.

    Also, do NOT use the word Date as the field name. Use PartDate or Part_Date or QuantityDate or Quantity_Date or some such name. And do not use spaces in the name either. Field names will work with spaces, but it is not recommended and it complicates things down the road.

    Thursday, July 16, 2020 3:54 PM
  • You shouldn't repeat the descriptions, that's superfluous. Instead, store the data in two related tables.

    Create a new table tblParts with fields Part (number, long integer, primary key) and Description (short text, length as appropriate)

    Also create a new table tblQuantities with fields Part (number, long integer), Date (date/time) and Quantity (number, long integer)

    [I'd prefer to give the date field a different name since Date is also a data type and a built-in function]

    Create a relationship between tblParts and tblQuantities on Part vs Part, and set 'Enforce referential integrity' for this relationship.

    Create and run the following VBA procedure. tblData is the name of the existing table.

    Sub TransposeData()
        Dim dbs As DAO.Database
        Dim rstData As DAO.Recordset
        Dim rstParts As DAO.Recordset
        Dim rstQuantities As DAO.Recordset
        Dim i As Long
        Set dbs = CurrentDb
        Set rstData = dbs.OpenRecordset("tblData", dbOpenForwardOnly)
        Set rstParts = dbs.OpenRecordset("tblParts", dbOpenDynaset)
        Set rstQuantities = dbs.OpenRecordset("tblQuantities", dbOpenDynaset)
        Do While Not rstData.EOF
            rstParts.AddNew
            rstParts!Part = rstData!Part
            rstParts!Description = rstData!Description
            rstParts.Update
            For i = 2 To rstData.Fields.Count - 1
                If Not IsNull(rstData.Fields(i)) Then
                    rstQuantities.AddNew
                    rstQuantities!Part = rstData!Part
                    rstQuantities![Date] = CDate(rstData.Fields(i).Name)
                    rstQuantities!Quantity = rstData.Fields(i)
                    rstQuantities.Update
                End If
            Next i
            rstData.MoveNext
        Loop
        rstQuantities.Close
        rstParts.Close
        rstData.Close
    End Sub


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Thursday, July 16, 2020 3:58 PM