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

[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

 

[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]...

 

[Michel Walsh]

Hi,


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


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

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

 

[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

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

 

[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]

 

[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

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

 

[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

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

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