Results 1 to 2 of 2
2002-12-16, 14:49 #1
- Join Date
- Jul 2002
- Petersburg, Virginia
- Thanked 0 Times in 0 Posts
Sort Order in Crosstab Query (XP)
I have a crosstab query "qryXdata" that I cannot get to sort records as desired. QryXdata is based on "qryMoData" which holds monthly data. Critical fields are:
Indicator (text data type)
Month (long integer)
Year (long integer)
Records in qryMoData should appear chronologically. Therefore, Sort order is (1) Year, (2) Month. There is a calculated field that combines both fields and adds a leading zero to single digit months so that Access doesn't sort Feb (2) before Oct (10). The "Period" field = Period: IIf(Len([datamonth])=1,"0" & [datamonth],[datamonth]) & "/" & Right([datayear],2). Records sort as desired.
With some types of monthly data I want to view data from two related Indicators, so the qryXdata Crosstab query uses same 4 fields; each Indicator becomes a column in the qryXdata results. (Results are ultimately transferred to Excel for graphing.) Unfortunately, the results sort by the Period field ... 07/01, 07/02, 08/01, 08/02, etc. so that month, not year, is essentially the basis for the sort.
How can I get qryXdata to retain sort specified in qryMoData or somehow re-sort in the desired manner?
2002-12-16, 14:56 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Sort Order in Crosstab Query (XP)
Because you have month before year in the definition of Period, you will sort on month and then on year. Try switching them around, or define Period as a numeric value:
This will result in values like 200108, 200211 etc.