Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Query Column Sort (Access97)

    I'm trying to sort by month and year for a 6 month period in a crosstab query.
    Expr1: Format([LAST_SRVC_DATE],"mm/yy") is my column heading sorted ascending.
    This is producing 01/02, 02/02, 03/02, 10/01, 11/01, 12/01
    Anything I try won't sort it correctly
    I want it to display as:
    10/01 11/01 12/01 01/02 02/02 03/02
    Help! Its late and my brain hurts
    Attached Images Attached Images

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Column Sort (Access97)

    Open your query in design view.

    Click on the Column Heading (Crosstab Row) and open Properties.

    In Column Headings, type in the dates in the order you want, as in <font color=blue>"11/01","12/01","01/02","02/02"</font color=blue>, etc. Include the quotes (maybe single quotes, to keep things consistent in your query).

    HTH,

    Tom

  3. #3
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Column Sort (Access97)

    Add the following to the query: Year([YourTbl].[DateField])*12+DatePart("m",[YourTbl].[DateField])-1 and order by that

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Column Sort (Access97)

    Tom,
    Should have stated that these aren't static values. Every month a new month is added and the earliest month drops off.
    Is there a way to automatically fill the column headings?
    Scott

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Column Sort (Access97)

    Rich,
    That sorted the colums correctly but the column labels are 24021 24022 24023 24024 24025 24026
    not the mm/yy
    Scott

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Column Sort (Access97)

    Scott,

    Attached is an Access 97 db that shows how to use a rolling crosstab report - it seems to do what you want.

    This one is designed for twelve months of data, but you can modify it to meet your needs.

    HTH,

    Tom
    Attached Files Attached Files

  7. #7
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Column Sort (Access97)

    Don't output the field I gave you, just set it to Where on the total field

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Column Sort (Access97)

    Rich,
    When I try to sort on that field I get this message
    Attached Images Attached Images

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query Column Sort (Access97)

    Thanks Tom,
    I will use that if I can't figure this one out. I already have the dynamic report built.
    Just need to sort it correctly
    Thanks

Posting Permissions

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