I'm trying to sort the colums by month/year
10/01 11/01 12/01 01/02 02/02 03/02
here is the query:
TRANSFORM Sum(tblFBA_DATA.CALLS) AS [The Value]
SELECT tblFBA_DATA.NAME
FROM tblFBA_DATA
GROUP BY tblFBA_DATA.NAME
PIVOT Format([LAST_SRVC_DATE],"mmm/yy");
The value for the months isn't static, it is a live 6 month view looked at every month.
Someone gave me this formula to use and sort by:
(Year([DATE])*12+Month([DATE])-1)
It sorts the columns right but the values are 24021 to 24026 not the mmm/yy that I need.
If I try to use it in another column in the query and sort on it I get the message:
"You tried to execute a query that doesn't include the specific expression .see above. as part of an aggregate function. "
Has anyone had luck sorting like this? Help!
Scott

I wonder which kind soul gave you that formula? http://www.access-programmers.co.uk/ubb/smile.gif
Send me some sample data in 97 and zipped and I'll take a look for you
rich@richies50.fsnet.co.uk

The problem is that mm/yy is a string NOT a date. Therefore the columns will be sorted as text fields. This puts month first so you would see 01/02 02/02 03/02 10/01 11/01 12/01. If you want them to sort in year - month order, you'll need to change the format to yy/mm. To avoid confusion, I would use a four digit year - yyyy/mm.

Is there anyway to create critera within a crosstab query that takes daily values and sums them by DateValue that is formated using Format([DateValue],"yyyy/mm").

I want to specify the starting Month and Year and ending Month and year? Or must that first be performed in another query and the a crosstab query that pulls from the filtered query? But the only whay I can think of doind that in another query is to have the user input the starting and ending DATE but I want them to only specify the stating and ending Month-Year??
:confused:

Where Format(YourDate,"yyyy/mm") Between [Enter starting yyyy/mm] AND [Enter ending yyyy/mm];

As long as you use year/month order you can do a range check.

Where Format(YourDate,"mm/yyyy") Between [Enter starting mm/yyyy] AND [Enter ending mm/yyyy]; - WILL NOT WORK