Hi,

I have a table with three columns ID / RefDate / RcvdDate. I need to transform the data into three other columns, one labeled ID, one labeled Date and the other labeled type . e.g.

ID | RefDate |Rcvd Date
----------------------------
1 01/01/04 02/01/04
2 05/01/04 07/01/04
3 07/01/04 12/01/04

to

ID | Date | Type
----------------------------
1 01/01/04 RefDate
1 02/01/04 RcvdDate
2 05/01/04 RefDate
2 07/01/04 RcvdDate
3 07/01/04 RefDate
3 12/01/04 RcvdDate

How can I do this

Jon

Hi,

   If this is a one time ETL, I would export the table to Excel and do it there. Copy, Paste, etc.

   You will not get the duplicate ID fields to work, however. Youmay need to push these to another table that looks up the ID.

HTH

mmcdonal

Hi,

I would do it this way

Sub Transpose()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rst1 As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst.Open "Table1", cnn
rst1.Open "Table2", cnn, adOpenDynamic, adLockOptimistic, acTable
Do While Not rst.EOF
    rst1.AddNew
    rst1.Fields(0) = rst.Fields(0)
    rst1.Fields(1) = rst.Fields(1)
    rst1.Fields(2) = "RefDate"
    rst1.Update
    rst1.AddNew
    rst1.Fields(0) = rst.Fields(0)
    rst1.Fields(1) = rst.Fields(2)
    rst1.Fields(2) = "RcvdDate"
    rst1.Update
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
rst1.Close
Set rst1 = Nothing
cnn.Close
Set cnn = Nothing

End Sub

-vemaju

This would work but it would add a new primary key to each field, so the result would be:

ID1 ID2 Date Type
1 1 01/01/04 RefDate
2 1 02/01/04 RcvdDate
3 2 05/01/04 RefDate
4 2 07/01/04 RcvdDate
5 3 07/01/04 RefDate
6 3 12/01/04 RcvdDate

Unless you decided not to use indexing or a PK.


mmcdonal

Actually it wouldn't because you are transposing the PKs twice, so the PK would not be indexed.

I hope you don't need the table to do anything.

mmcdonal

Thanks all for your response,

Basically I need to massage the data from a base table (first format) into a query (second format). From what has been posted I guess I am going to have to put the lot into a table (as per vemaju's suggestion) and then run a query of of that.

thanks again.

Jon

sorry to come in late on this one, but you can just do a union query
SELECT ID, RefDate, 'RefDate' AS Type
FROM <<Table>>
UNION SELECT ID, RcvdDate, 'RcvdDate' AS Type
FROM <<Table>>;

hth
Phil

YOU ARE A DIAMOND!!!

Thanks Phil. Your method is perfect for this!!! :D Thanks so much!

Jon

Sorry to take a lift on the topic. But i would be interested to know if the
inverse of the problem would be possible to be solved by a union query ?
Looking to the two tables on the initial topic, how would i starting with
the final table have the first as the result from the union ?
Penta.

Hi penta,

No a union query wouldn't solve the inverse problem. It seemed to me that the original problem was not one of transposing rows, but rather how to make 1 row into 2! that immediately made me think of union.

The inverse problem is how to make 2 rows into 1, so that makes me think of group by - but we also have the problem of how to make 1 column (the date) into 2 columns so because this is Access that makes me think of the transform/pivot statement. Maybe something like this would do it (I'm not a big user of Access so I'm not very familiar with the pivot syntax - the SQL below came fome the Access crosstab query wizard):
TRANSFORM Max([Date]) AS [The Value]
SELECT [ID]
FROM <<Table>>
GROUP BY [ID]
PIVOT [Type];

rgds
Phil