How do I convert row data to column data in Access design query

  • Thread starter Transpose data in rows to columns
  • Start date
T

Transpose data in rows to columns

Hi,

I'm in the process of developing a report in Access, through an ODBC query.
The sourced data comes over as follows:

Part Month Qty Sold
ABC 1 200
ABC 4 350

Is there a way to convert row data to column data. In other words, I'm
attempting to convert the aforementioned data, as follows:

Part Month 1 Month 2 Month 3 Month 4
ABC 200 350

Please advise.................thank you, Pete
 
M

Michel Walsh

Hi,


You could use a crosstab query, like:


TRANSFORM SUM( [qty sold] )
SELECT part
FROM myTable
GROUP BY part
PIVOT "m" & [Month] IN("m1", "m2", "m3", "m4")



Hoping it may help,
Vanderghast, Access MVP



"Transpose data in rows to columns" <Transpose data in rows to
(e-mail address removed)> wrote in message
news:[email protected]...
 
M

Michel Walsh

Hi,


.... I assume [Month] holds a numerical value like 1, 2, 3, .... not "jan",
"fev", ...


Hoping it may help,
Vanderghast, Access MVP
 
T

Transpose data in rows to columns

Michel,

Thank you for your feedback. Unfortunately I'm a novice when it comes to
Access. However, I'll research further how to execute a crosstab query, as
suggested below.

Thanks Again,
Pete

Michel Walsh said:
Hi,


You could use a crosstab query, like:


TRANSFORM SUM( [qty sold] )
SELECT part
FROM myTable
GROUP BY part
PIVOT "m" & [Month] IN("m1", "m2", "m3", "m4")



Hoping it may help,
Vanderghast, Access MVP



"Transpose data in rows to columns" <Transpose data in rows to
(e-mail address removed)> wrote in message
Hi,

I'm in the process of developing a report in Access, through an ODBC
query.
The sourced data comes over as follows:

Part Month Qty Sold
ABC 1 200
ABC 4 350

Is there a way to convert row data to column data. In other words, I'm
attempting to convert the aforementioned data, as follows:

Part Month 1 Month 2 Month 3 Month 4
ABC 200 350

Please advise.................thank you, Pete
 
M

Michel Walsh

Hi,

You can also use the query wizard (for crosstab) in this case, to get
almost the job all done. Only the field name produced are probably not as
you wanted, and the IN list (in the PIVOT clause) would probably be
missing.


Hoping it may help,
Vanderghast, Access MVP

"Transpose data in rows to columns"
Michel,

Thank you for your feedback. Unfortunately I'm a novice when it comes to
Access. However, I'll research further how to execute a crosstab query, as
suggested below.

Thanks Again,
Pete

Michel Walsh said:
Hi,


You could use a crosstab query, like:


TRANSFORM SUM( [qty sold] )
SELECT part
FROM myTable
GROUP BY part
PIVOT "m" & [Month] IN("m1", "m2", "m3", "m4")



Hoping it may help,
Vanderghast, Access MVP



"Transpose data in rows to columns" <Transpose data in rows to
(e-mail address removed)> wrote in message
Hi,

I'm in the process of developing a report in Access, through an ODBC
query.
The sourced data comes over as follows:

Part Month Qty Sold
ABC 1 200
ABC 4 350

Is there a way to convert row data to column data. In other words, I'm
attempting to convert the aforementioned data, as follows:

Part Month 1 Month 2 Month 3 Month 4
ABC 200 350

Please advise.................thank you, Pete
 
J

John Vinson

Hi,

I'm in the process of developing a report in Access, through an ODBC query.
The sourced data comes over as follows:

Part Month Qty Sold
ABC 1 200
ABC 4 350

Is there a way to convert row data to column data. In other words, I'm
attempting to convert the aforementioned data, as follows:

Part Month 1 Month 2 Month 3 Month 4
ABC 200 350

Please advise.................thank you, Pete

Try the Crosstab Query Wizard in the New Queries button.

John W. Vinson[MVP]
 
T

tom4you

How would you do this for multiple groups - not just for ABC with 2 rows but
then also for BCD with four rows or CDE with 7 rows, etc.
--
Tom


Michel Walsh said:
Hi,


You could use a crosstab query, like:


TRANSFORM SUM( [qty sold] )
SELECT part
FROM myTable
GROUP BY part
PIVOT "m" & [Month] IN("m1", "m2", "m3", "m4")



Hoping it may help,
Vanderghast, Access MVP



"Transpose data in rows to columns" <Transpose data in rows to
(e-mail address removed)> wrote in message
Hi,

I'm in the process of developing a report in Access, through an ODBC
query.
The sourced data comes over as follows:

Part Month Qty Sold
ABC 1 200
ABC 4 350

Is there a way to convert row data to column data. In other words, I'm
attempting to convert the aforementioned data, as follows:

Part Month 1 Month 2 Month 3 Month 4
ABC 200 350

Please advise.................thank you, Pete
 
C

Conan Kelly

tom4you,

Try this,

--Create a new query in design view
--Add your ODBC query to it
--Add your Part, Month, and Qty Sold fields/columns to the grid
--Change to a Crosstab Query (click Query menu > Crosstab Query)
--You'll notice there are 2 new rows in the grid: "Total:" and "Crosstab:"
--Make sure you set the "Crosstab:" row for "Part" to "Row Heading", for
"Month" to "Column Heading", and for "Qty Sold" to "Value"
--Make sure you set the "Total:" row for "Part" and "Month" to "Group By",
and for "Qty Sold" to "Sum"

This query will handle all part numbers for any number of months.

HTH,

Conan




tom4you said:
How would you do this for multiple groups - not just for ABC with 2 rows
but
then also for BCD with four rows or CDE with 7 rows, etc.
--
Tom


Michel Walsh said:
Hi,


You could use a crosstab query, like:


TRANSFORM SUM( [qty sold] )
SELECT part
FROM myTable
GROUP BY part
PIVOT "m" & [Month] IN("m1", "m2", "m3", "m4")



Hoping it may help,
Vanderghast, Access MVP



"Transpose data in rows to columns" <Transpose data in rows to
(e-mail address removed)> wrote in message
Hi,

I'm in the process of developing a report in Access, through an ODBC
query.
The sourced data comes over as follows:

Part Month Qty Sold
ABC 1 200
ABC 4 350

Is there a way to convert row data to column data. In other words, I'm
attempting to convert the aforementioned data, as follows:

Part Month 1 Month 2 Month 3 Month 4
ABC 200 350

Please advise.................thank you, Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top