Convert rows to columns

Question

What is the best way to do this in Access?

Create table tmp
(
  plant int,
  material vchar(20),
  workcenter int,
  setuptime vchar(20)
)

insert into tmp values( 1, mat1, 2, 30)
insert into tmp values( 1, mat1, 3, 30)
insert into tmp values( 1, mat2, 3, 30)
insert into tmp values( 1, mat2, 4, 30)
insert into tmp values( 2, mat1, 4, 30)
insert into tmp values( 2, mat1, 5, 30)

Result needs to look like.

Plant  Material  Workcenter1  Setuptime1  Workcenter2  Setuptime2
1      Mat1      2            30          3            30
1      Mat2      3            30          4            30
2      Mat1      4            30          5            30

I was thinking that this might work, but there has to be a better way.

SELECT t.Plant, 
    t.Material, 
    t.Workcenter as Workcenter1, 
    t.setuptime as SetupTime1
    t2.Workcenter as Workcenter2, 
    t2.setuptime as SetupTime2
FROM tmp t
LEFT JOIN tmp t2
  on t.plant = t2.plant
  and t.material = t2.material

Thanks a bunch.

Answer 1

I don't know access syntax well enough to give you a complete answer, but for help with your own investigations I can tell you that what you are trying to do is called a "crosstab" query or "pivot".

Using those keywords in google should help you out.

Answer 2

There is a crosstab option in the Query Wizard in access which lays your table out horizontally. Isn't that what you're looking for?

Answer 3

Have you tried a Crosstab Query?

EDIT

(I grok better after reformatting your question)

You solution will almost work. But what you will get will have double entries (like this):

Plant  Material  Workcenter1  Setuptime1  Workcenter2  Setuptime2
1      Mat1      2            30          2            30
1      Mat1      2            30          3            30
1      Mat1      3            30          2            30
1      Mat1      3            30          3            30
1      Mat2      3            30          4            30
1      Mat2      3            30          3            30
1      Mat2      4            30          3            30
1      Mat2      4            30          4            30
2      Mat1      4            30          5            30
2      Mat1      4            30          4            30
2      Mat1      5            30          5            30
2      Mat1      5            30          4            30

And if you have more than two Work Centers then it gets even worse.

So what you want is a column indicator

Create table tmp
(
  plant int,
  material vchar(20),
  workcenter int,
  setuptime vchar(20),
  myCol int
);

(btw., vchars won't work in ms-access/Jet... chars will though)

insert into tmp values( 1, mat1, 2, 30, 1);
insert into tmp values( 1, mat1, 3, 30, 2);
insert into tmp values( 1, mat2, 2, 30, 1);
insert into tmp values( 1, mat2, 3, 30, 2);
insert into tmp values( 2, mat1, 4, 30, 1);
insert into tmp values( 2, mat1, 5, 30, 2);

SELECT tmp.plant, tmp.material, tmp.workcenter,   tmp.setuptime,
                                tmp_1.workcenter, tmp_1.setuptime
FROM tmp INNER JOIN tmp AS tmp_1 ON (tmp.material = tmp_1.material)
    AND (tmp.plant = tmp_1.plant)
WHERE   (((tmp.myCol)=1)
    AND ((tmp_1.myCol)=2));

Of course if you can edit the original table, maybe you want to just add a second Workcenter and Setuptime column,

Answer 4

Your sample output isn't clear -- I think it's meant to look like:

Plant | Mat  | Wkcntr1 | STime1 | Wkcntr2 | STime2 
1     | Mat1 | 2       | 30     | 3       | 30 
1     | Mat2 | 3       | 30     | 4       | 30 
2     | Mat1 | 4       | 30     | 5       | 30

I'd say that the table isn't ideally set up. It doesn't look like it has a primary key, so it's not clear which record belongs in the 1 columns and which goes in the 2 column.

I would add an extra field for RecordNumber (which is a 1 or a 2), then you have a primary key of Plant, Material, and RecordNumber.

Your query would then look like:

SELECT tmp.plant, tmp.material, tmp.workcenter AS W1, tmp.setuptime AS S1, tmp_1.workcenter AS W2, tmp_1.setuptime AS S2
FROM tmp INNER JOIN tmp AS tmp_1 ON (tmp.material = tmp_1.material) AND (tmp.plant = tmp_1.plant)
WHERE (((tmp.recordNum)=1) AND ((tmp_1.recordNum)=2));

This will only work with a limited (and already known) number of number thingies (whatever the 1 and 2 represent).

Answer 5

From what I understand of the problem you want to solve I don't think it's feasible using a standard SQL query (at least not in Access).

I tried to hack some code that does what you want (I think).

How to use it

Just copy/paste the code below in a VBA module.
From code, or, from the VBA IDE Immediate window if you want to test it, just call:

ExpandTable

Assumptions

• the table temp exists with the data you want to expand containing the plant/material/worksatation/etc
• the temp table is not empty (I have omitted some code checks to avoid bloating the sample).
• the expanded table will be created in a new table called result.

Code

Public Sub ExpandTable()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset, rs2 As DAO.Recordset
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Dim maxWorkCenters As Integer
    Dim i As Integer
    Dim sql As String

    Set db = CurrentDb

    ' Delete the old result table if there was one '
    On Error Resume Next
    db.TableDefs.Delete "result"
    On Error GoTo 0

    ' Create the result table '
    Set td = db.CreateTableDef("result")
    td.Fields.Append td.CreateField("Plant", dbInteger)
    td.Fields.Append td.CreateField("Material", dbText)
    ' Get the maximum number of workcenters we will need '
    ' for a given Plan/Material combination '
    sql = "SELECT Count(*) FROM Temp GROUP BY Plant, Material"
    Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
    maxWorkCenters = Nz(rs.Fields(0).Value, 0)
    rs.Close
    Set rs = Nothing
    ' Create as many columns as we need to fit all these combinations '
    For i = 1 To maxWorkCenters
        td.Fields.Append td.CreateField("WorkCenter" & i, dbText)
        td.Fields.Append td.CreateField("SetupTime" & i, dbInteger)
    Next i
    db.TableDefs.Append td

    ' Now get the data into the new table '
    Dim lastPlant As Variant, lastMaterial As Variant
    Dim curcol As Integer
    sql = "SELECT Plant, Material, Workcenter, Setuptime FROM Temp ORDER BY Plant, Material, WorkCenter"
    Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
    Set rs2 = db.OpenRecordset("result", dbOpenDynaset)
    With rs
        lastPlant = 0
        lastMaterial = ""
        Do While Not .EOF
            If (Nz(!Plant) <> lastPlant) Or (Nz(!Material) <> lastMaterial) Then
                If rs2.EditMode = dbEditAdd Then
                    ' Save the previously edited record if any '
                    rs2.Update
                End If

                ' Different plant/material, so we add a new result '
                rs2.AddNew
                rs2!Plant = !Plant
                rs2!Material = !Material
                rs2!WorkCenter1 = !WorkCenter
                rs2!SetupTime1 = !Setuptime
                lastPlant = Nz(!Plant)
                lastMaterial = Nz(!Material)
                curcol = 1
            Else
                ' Same plant/material combi, so we fill the next column set '
                curcol = curcol + 1
                rs2.Fields("Workcenter" & curcol).Value = !WorkCenter
                rs2.Fields("SetupTime" & curcol).Value = !Setuptime
            End If
            .MoveNext
        Loop
        If rs2.EditMode = dbEditAdd Then
            ' Save the last result '
            rs2.Update
        End If
    End With

    Set rs2 = Nothing
    Set rs = Nothing
    Set db = Nothing

End Sub

About the code

• The result table is entirely re-created every time you run ExpandTable.
• The number of additional WorkCenterX and SetupTimeX columns adapts to the actual number of unique Plant/Material pairs.
• You'll have to modify code to suit your exact needs.
• You'll also have to clean it up a bit as some things could probably be expressed a bit better but you get the jest.

Test database

You can download a test Access 2000 database from http://blog.nkadesign.com/wp-content/uploads/SO/SO547777.zip.

Anyway, hope it does what you want or at least gets your closer.