Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CrossTab Column Order (Access 97)

    I have produced a cross tab query to show hours worked(data) by user (rows) by month (columns) but the columns are order alphabetically (April,August,December etc) how can i change this to chronological order ?

    Using month numbers rather than names works but makes the report less friendly. I have built a months table with Jan - Dec in one field with 1-12 in another field but cannot introduce this table into the query as no other table has the month name in it. I am drawing this out via the query as Month:format([interviewdate],"mmmm").

    Any pointers gratefully received.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab Column Order (Access 97)

    Build a new query with the crosstab query as only table. In the new query, you can order the columns like you want.
    Francois

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: CrossTab Column Order (Access 97)

    When you say you can order the columns like you want, do you use the pivot line to do this?
    Pivot Month in ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug',' Sep','Oct','Nov','Dec') assuming that you are using the shortened version of the Month.
    Pat

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab Column Order (Access 97)

    That's not what I mean.
    Create a new query, and add the crosstab query as table. You become this:
    Attached Images Attached Images
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab Column Order (Access 97)

    just right, thank you for the idea.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CrossTab Column Order (Access 97)

    But why bother when putting column headings in the cross tab will accomplish the same thing with only a single query? Office knows about Month names and abbreviations, so it has no trouble allocating months apprpriately.
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab Column Order (Access 97)

    Do you know another method to have the names of the months as column heading and the columns sorted on the month number ?
    Francois

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: CrossTab Column Order (Access 97)

    Hi Francois,
    I don't know if it works in Access 97, but in 2000, if you run the crosstab query and then drag the months columns into the order you want, it saves the settings somehow! Can be a little confusing as if you then change sort orders from Ascending to descending for example, they don't all sort properly - any that you had rearranged remain in the order you put them in. Very odd.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: CrossTab Column Order (Access 97)

    I forgot to mention that I think what Charlotte is saying is that if you set the column headings of the crosstab query to "Jan", "Feb" etc and you're extracting the month as format([datefield],"mmm") then you'll get them in the correct order.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab Column Order (Access 97)

    Nice tricks. I just tried both in Access 97 and works OK.
    Every day I'm learning something new on the Lounge.
    Thanks
    Francois

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CrossTab Column Order (Access 97)

    Hi Fatherjack,
    Read the thread again, there are easier ways to do what you want than the way I say.
    Francois

Posting Permissions

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