I am building a query that would show six months and a total for each month. I am querying 3 fields Date, orders, and model code.

Please help me.

you need more explanation and samples to get help here.

Ok, I am using Access 2003,
The query I currently built Access two tables and has 3 fields. I am trying to gather date from an specific model codes, about 13 codes total. For these 13 codes I am needing to look at current months and next 5 months orders and have a sum for each month by code.I would like it to look something like this:

MC April May June July Aug Sept
24 1000 550 600 750 900 800
30 500 500 450 500 450 500
33 350 400 350 375 350 375

I am having trouble accomplishing this with one query, is it possible to have one query supply this data?

If not I'm thinking I need a append query for each model code and tables to place the data, then a additional query to extract data to one query/report.

Any suggestions?

Thanks,

Hi -

Here's a sample crosstab query that produces a monthly sum for each category over a six-month period, as well as a monthly average and an overall total.

The query makes use of two tables, tblCat with CatID and Category, and tblTransfer which contains payment date, amount, categoryID. You should be able to modify this by inserting your table/field names. When prompted, enter start date, e.g. 10/1/08 and end date 3/31/09. Note that you'll need to manually modify the pivot statement to agree with your date range.

PARAMETERS FromDate DateTime, ToDate DateTime;
TRANSFORM nz(Sum(tblTransfer.PayAmt),0) AS SumOfPayAmt
SELECT
tblCat.Category
, nz(Sum(tblTransfer.PayAmt),0) AS Total
, Format(Sum([tblTransfer].[PayAmt])/6,"$#.00") AS Avg
FROM
tblTransfer
LEFT JOIN
tblCat
ON
tblTransfer.CatID = tblCat.CatID
WHERE
(((CDate(Format([expdte],"mm/yyyy"))) Between [FromDate]
AND
[ToDate]))
GROUP BY
tblCat.Category
ORDER BY
tblCat.Category
, Format([expdte],"mmm yy")PIVOT Format([expdte],"mmm yy") In ("Oct 08","Nov 08","Dec 08","Jan 09","Feb 09"
, "Mar 09");

HTH - Bob

Thanks I will play around with it for awhile.