CrossTab report covering >1 year (Access 2000)

[stevecox4444]

I have a table of sales transactions covering a 3 year time period. I want to be able to produce a cross tab report that sums the sales for each month of the 3 years but shows them separately in correct order across the page Including year and month. eg Jan 2003 comes before april 2003 and all a specific year are grouped together in the correct order. Hope this makes sense and thanks in advance

[patt]

Try fiddling around with the PIVOT clause.

eg.
TRANSFORM Sum(tblPayment.Amount) AS SumOfAmount
SELECT tblPayment.PaymentMethod, Sum(tblPayment.Amount) AS [Total Of Amount]
FROM tblPayment
GROUP BY tblPayment.PaymentMethod
PIVOT Format([DatePaid],"mmm yy") In ("Jan 03","Feb 03","Mar 03","Apr 03","May 03","Jun 03","Jul 03","Aug 03","Sep 03","Oct 03","Nov 03","Dec 03");

[stevecox4444]

Thanks for the amazingly quick response. I am guessing that you mean in the Forms - create Pivot Table Wizard section - If so I have a new problem - When I select the appropriate fields and click on next, I get a message "The class argument in the CreateObject function of the Visual Basic Procedure you're trying to run is invalid"
A - am I guessing correctly on needing to be in the Pivot Table area
B - If so , any idea what the error message means

[patt]

No, I mean the Query - Create Crosstab query.
You will need to go into the SQL of the query after you have used the query wizard to initially create the crosstab query and change or introduce the PIVOT clause like my first reply suggests.

[stevecox4444]

Thanks for clarification (and again the speed)-
did that but when I try to run the query, it is looking for ODBC data links (select Data Source- while the data was originally created via ODBC, to speed things up, I create a local (SP) table in my database and update it when I first go in. It is this updated local table that is the source for my cross tab query
To explain more fully: I want Item Number and Item Name as the rows in my cross tab and sum of quantity as the data with it grouped by date using yor suggested formt. The resultant SQL is below

TRANSFORM Sum(TblItemSales.Quantity) AS SumOfQuantity
SELECT TblItemSales.ItemNumber, TblItemSales.ItemName, Sum(TblItemSales.Quantity) AS [Total Sold]
FROM TblItemSales
GROUP BY TblItemSales.ItemNumber, TblItemSales.ItemName
PIVOT Format([Date],"mmm yy") In ("Jan 03","Feb 03","Mar 03","Apr 03","May 03","Jun 03","Jul 03","Aug 03","Sep 03","Oct 03","Nov 03","Dec 03","Jan 04","Feb 04","Mar 04","Apr 04","May 04","Jun 04","Jul 04","Aug 04","Sep 04","Oct 04","Nov 04","Dec 04")

TIA
Steve

[stevecox4444]

Got it - thanks to your help Pat - its not as pretty as yours but it works

TRANSFORM Sum(TblItemSales.Quantity) AS SumOfQuantity
SELECT TblItemSales.ItemNumber, TblItemSales.ItemName, Sum(TblItemSales.Quantity) AS [Total Of Quantity]
FROM TblItemSales
GROUP BY TblItemSales.ItemNumber, TblItemSales.ItemName
PIVOT Format([Date],"yyyy/ mm");
Where its not as pretty as yours is that it shows 2003/8 then 2003/9 then 2003/10 etc but it is in right order and is very useable
Thanks again for your help
Steve