Turning 1 line into 2 columns

[Maracay]

Hi guys, I have this table (just one table)

Date, Category, Quantity-1, Quantity-2
01/02/08, 1,100
01/02/08, 1, 10
01/02/08, 2, 20
02/02/08, 1, 50
02/02/08, 1, 10
02/02/08, 2, 60
02/02/08, 2, 35
And so on……

I need this query

Date, Category-1, Category-2
01/02/08, 110, 20
02/02/08, 60, 95

Basically what I want is 1 record per day, and aggregates the quantities by
category for that day, but I want to place the aggregated quantities in 2
different columns since I want to use this query for a graphic.

Any help will be appreciated

 

[Dale Fye]

Create a crosstab query,

Add the Date (you really should use another name for the field. Date is a
reserved word, so you should do something like TransactionDate or something
that is better at identifying the type of date), Category, and Quantity-1
field to your query.

Change the query type to Crosstab

Select RowHEader in the Crosstab row under [Date], ColumnHeader, under
[Category], and Value under [Quantity-1]

Then change the Total row to Sum under [Quantity-1]

To make it look exactly like yours, you should change the Field row where
you have [Category] to look like Expr1: "Category-" & [Category]

The SQL will look like:

TRANSFORM Sum(tbl_Crosstab.[Quantity-1]) AS [SumOfQuantity-1]
SELECT tbl_Crosstab.SomeDate
FROM tbl_Crosstab
GROUP BY tbl_Crosstab.SomeDate
PIVOT "Category-" & [Category];

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Maracay]

Thanks, exactly what I wanted

Create a crosstab query,

Add the Date (you really should use another name for the field. Date is a
reserved word, so you should do something like TransactionDate or something
that is better at identifying the type of date), Category, and Quantity-1
field to your query.

Change the query type to Crosstab

Select RowHEader in the Crosstab row under [Date], ColumnHeader, under
[Category], and Value under [Quantity-1]

Then change the Total row to Sum under [Quantity-1]

To make it look exactly like yours, you should change the Field row where
you have [Category] to look like Expr1: "Category-" & [Category]

The SQL will look like:

TRANSFORM Sum(tbl_Crosstab.[Quantity-1]) AS [SumOfQuantity-1]
SELECT tbl_Crosstab.SomeDate
FROM tbl_Crosstab
GROUP BY tbl_Crosstab.SomeDate
PIVOT "Category-" & [Category];

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi guys, I have this table (just one table)

Date, Category, Quantity-1, Quantity-2
01/02/08, 1,100
01/02/08, 1, 10
01/02/08, 2, 20
02/02/08, 1, 50
02/02/08, 1, 10
02/02/08, 2, 60
02/02/08, 2, 35
And so on……

I need this query

Date, Category-1, Category-2
01/02/08, 110, 20
02/02/08, 60, 95

Basically what I want is 1 record per day, and aggregates the quantities by
category for that day, but I want to place the aggregated quantities in 2
different columns since I want to use this query for a graphic.

Any help will be appreciated