1

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.

Improve this question

5 Answers 5

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

Comments

1

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,

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

Not quite the same as cross tab or pivot. These type have aggregation, though an aggregate of min might work.
0

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?

Improve this answer

1 Comment

Cross-tabs use the data has columns. I'm not using that. It is really denormalization for an import into another system.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.