Transpose data from rows in column

[Guest]

Hi All,
I need to "transpose" data from Table1 to Table2.
Table1 has 27 fields: PartNumber, Week1, Week2, ... thru Week26.
I need to append all the above data into Table2 fields: PartNumber,WkNum,
Qty. This means creating 26 rows of data in Table2 per one row in Table1.

The mapping would be as follows:
Table1.PartNumber --> Table2.PartNumber
Table1.Week1 --> Table2.Qty (depending on the week number)

Table2.WkNum would be the week number corresponding to the Week fields 1-26.
Hope this is not too confusing. Any help is very much appreciated.

 

[Douglas J Steele]

You should be able to use a Union query (or probably more than 1, as I think
there's a limit to how many tables can appear in a single Union query)

SELECT PartNumber, 1 As WkNum, Week1 As Qty
FROM Table1
UNION
SELECT PartNumber, 2 As WkNum, Week2 As Qty
FROM Table1
UNION
SELECT PartNumber, 3 As WkNum, Week3 As Qty
FROM Table1

UNION
SELECT PartNumber, 26 As WkNum, Week26 As Qty
FROM Table1

Use that query (or queries) to populate the Table2 (assuming you're going to
get rid of Table1), or simply use it instead of creating a new table (if
you're obligated to keep Table1)

 

[Guest]

Try something like

Function AppendData()
Dim MyDB As DAO.Database, Table1 As DAO.Recordset, Table2 As DAO.Recordset
Dim I As Integer

Set MyDB = CurrentDb
Set Table1 = MyDB.OpenRecordset("Select * From Table1")
Set Table2 = MyDB.OpenRecordset("Select * From Table2")
While Not Table1.EOF
For I = 1 To 26
Table2.AddNew
Table2!PartNumber = Table1!PartNumber
Table2!Qty = Table1("Week" & I)
Table2.Update
Next I
Table1.MoveNext
Wend
End Function

Note: I didn't try this code, but I hope it will provide you with the right
idea

 

[Douglas J Steele]

You'll also want:

Table2!WkNum = I

 

[Guest]

Yes, I missed that, thank you Douglas.