Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 15th, 2005, 09:00 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default Transpose Rows

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
 
Old March 15th, 2005, 12:40 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 15th, 2005, 12:45 PM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old March 15th, 2005, 12:58 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 15th, 2005, 01:00 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 16th, 2005, 03:29 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 16th, 2005, 05:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old March 16th, 2005, 05:27 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

YOU ARE A DIAMOND!!!

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

Jon
 
Old March 16th, 2005, 03:24 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old March 17th, 2005, 05:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Transpose Rows/Pivot Table prasanta2expert Access 0 April 26th, 2008 01:01 AM
Transpose the data yogeshyl Excel VBA 2 September 15th, 2007 11:09 AM
Transpose Tables abc052107 Visual Studio 2005 0 August 23rd, 2007 12:40 AM
Help with transpose fegasa Excel VBA 3 April 7th, 2006 03:43 AM
transfer and transpose sham Excel VBA 4 March 6th, 2005 12:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.