Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
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 November 26th, 2006, 06:43 AM
Registered User
 
Join Date: Nov 2006
Location: khargone, madhya pradesh, India.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jaincafe
Default How to Transpose this type data

Hi,

I have a table with three columns no / value / amount. I need to transform the data into 17 other columns, one labeled no, one labeled value and the other labeled amount . e.g.

no value amount
0 a1 1
0 a2 2
0 a3 3
0 a4 4
0 a5 5
0 a6 6
0 a7 7
0 a8 8
0 0 9
0 a8 10
0 a7 11
0 a6 12
0 a5 12
0 a4 14
0 a3 15
0 a2 16
0 a1 17
10 a1 425
10 a2 525
10 a3 485
10 a4 425
10 a5 625
10 a6 525
10 a7 725
10 a8 725
10 0 715
10 a8 715
10 a7 735
10 a6 745
10 a5 625
10 a4 515
10 a3 445
10 a2 325
10 a1 275

to

    a1 a2 a3 a4 a5 a6 a7 a8 0 a8 a7 a6 a5 a4 a3 a2 a1
0 1 2 3 4 5 6 7 8 9 10 11 12 12 14 15 16 17
10 425 525 485 425 625 525 725 725 715 715 735 745 625 515 445 325 275


How can I do this



 
Old November 27th, 2006, 05:58 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

Jain,

You need a crosstab (pivot) query, using your column names, should be something like this:

=====================================
TRANSFORM Table1.amount
SELECT Table1.number
FROM Table1
GROUP BY Table1.number
PIVOT Table1.value;
=====================================

You will run into problems with your field names, might wanna change.

Lee




Similar Threads
Thread Thread Starter Forum Replies Last Post
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
Data Type error but it is the correct type Mitch SQL Server 2000 2 March 19th, 2004 11:31 AM





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