Crosstab query rewults to Chart

[Guest]

I am using Access 2002. I have a crosstab query that is delivering the data
I want but am having a problem getting the results to show up the way I want.

I am returning a count of projects by Manager for each month in the data.
The data will store a rolling 12 months of data. Right now there is only
three months of data: months 11, 12 and 1. The month in this case for each
record is stored as: month/01/year. So the dates in the month column are
11/01/06, 12/01/06 ane 1/1/07. If I run the crosstab query it produces the
totals correctly. If I display the entire date, and have the sort order set
to ascendinig order, he order of the months is correct. However, what I want
to do is to have just the 3 character name of the month (Jan, Feb, etc)
display on the chart. If I format the month to produce what I want, the order
of the months is not corred.

I hope this makes sense. I need to know how to display the 3 character name
of the month and still keep the months in coronlogical order.

Any help is appreciated.

Mr B

 

[Guest]

You should be able to sort your crosstab in the row source without displaying
a value. You can also provide some formatting in the the chart control.

 

[Guest]

Duane,

Thanks for the reply. I am using the following query to produce the count
per AVP per month.

TRANSFORM Count(qryFromCapturedData.KEY) AS CountOfKEY
SELECT qryFromCapturedData.AVP
FROM qryFromCapturedData
WHERE ((Not (qryFromCapturedData.AVP) Is Null))
GROUP BY qryFromCapturedData.AVP
ORDER BY qryFromCapturedData.AVP, qryFromCapturedData.CapturedMonth
PIVOT qryFromCapturedData.CapturedMonth;

Using this query, the colums are sorted in the cronological order that I
want, howver, the colunm headings are the dates (11/01/06, 12/01/06 and
1/1/07). What I want for the column headings is "Nov, Dec, Jan" in that order
(matches the dates from above)

How can I show "Nov, Dec, Jan" in the chart and not the actual dates?

Mr B

 

[Guest]

If you can't format the chart scale labels then you may need to change the
query sql to:
SELECT qryFromCapturedData.AVP,
Sum(Abs(Month(CapturedMonth)=1)) As Jan,
Sum(Abs(Month(CapturedMonth)=2)) As Feb,
Sum(Abs(Month(CapturedMonth)=3)) As Mar,
-- etc --
FROM qryFromCapturedData
WHERE ((Not (qryFromCapturedData.AVP) Is Null))
GROUP BY qryFromCapturedData.AVP
ORDER BY qryFromCapturedData.AVP;