Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Petersburg, Virginia
    Posts
    54
    Thanks
    0
    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)
    Data (single)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    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:

    Period: 100*[DataYear]+[DataMonth]

    This will result in values like 200108, 200211 etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •