Using Crosstab query Wizard to sum the quantities in a month.

[Guest]

I have a very "simple" task. I have one table with [Part Number], [Qty],
[Date]. This is a production forecast for the next 12 Months. I would like to
crosstab this information by Row=[Part Number], Column=[Date by Month], Data
Field =[Sum of Qty]. The table will be updated at least once a week and I
would rather not rebuild the query and Form every week. The form will be a
Datasheet with the first column the [Part Number], 2nd the Total of the 12
months, the third (here is where I run into trouble) is This Month, Next
Month... Last Month.
When I run the Crosstab Wizard I do get the data as I want it, but the
Column Headings have no Year. Is there a way to include the year without
renameing the headings?

 

[Guest]

Try this substitue your table name for Harry.
TRANSFORM Sum(Harry.Qty) AS SumOfQty
SELECT Harry.[Part Number], Sum(Harry.Qty) AS [Total Of Qty]
FROM Harry
WHERE (((Harry.Date) Between Date()-Day(Date())+1 And
DateAdd("m",11,Date()-Day(Date()))))
GROUP BY Harry.[Part Number]
PIVOT Format([Date],"yyyy - mm - mmm");

 

[Guest]

Thank you so very much. This works perfectly.

Try this substitue your table name for Harry.
TRANSFORM Sum(Harry.Qty) AS SumOfQty
SELECT Harry.[Part Number], Sum(Harry.Qty) AS [Total Of Qty]
FROM Harry
WHERE (((Harry.Date) Between Date()-Day(Date())+1 And
DateAdd("m",11,Date()-Day(Date()))))
GROUP BY Harry.[Part Number]
PIVOT Format([Date],"yyyy - mm - mmm");

--
KARL DEWEY
Build a little - Test a little

I have a very "simple" task. I have one table with [Part Number], [Qty],
[Date]. This is a production forecast for the next 12 Months. I would like to
crosstab this information by Row=[Part Number], Column=[Date by Month], Data
Field =[Sum of Qty]. The table will be updated at least once a week and I
would rather not rebuild the query and Form every week. The form will be a
Datasheet with the first column the [Part Number], 2nd the Total of the 12
months, the third (here is where I run into trouble) is This Month, Next
Month... Last Month.
When I run the Crosstab Wizard I do get the data as I want it, but the
Column Headings have no Year. Is there a way to include the year without
renameing the headings?