Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Ordering Xtab Column headings (XP)

    Dera loungers,

    I have a crosstab query which has column headers from atable where I am using the Status field, I want to order these across the top of the crosstab based on the Prtseq field which holds a numerical value for instance in this case:

    Rec: 1, Status: new, PrtSeq: 10
    Rec: 2, Status: live, PrtSeq: 99
    Rec: 1, Status: approved, PrtSeq: 20

    I want the column headings to be "New", "Approved", "Live".

    How do i sequence based on a "third-party field? - I did think that a simple query with the given sort sequence and then used in the crosstab, rather than the table would do it, sadly it doesn't.

    any ideas

    liz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ordering Xtab Column headings (XP)

    Hi liz,

    The column headers are always in alphabetical order unless you specify them explicitly.

    Open the crosstab query in design view.
    Activate the Properties window if necessary.
    Click in an empty part of the upper part of the query window to see the properties of the query as a whole.
    Enter the column headings in the desired order in the Column Headings property, just the way you entered them in your post:

    "New", "Approved", "Live"

    You can also use the Column Headings property to display only part of the available items. Only those explicitly listed will be shown.

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Ordering Xtab Column headings (XP)

    Hans,

    Thank you, I know about this but it does mean the thing is hard wired and not data driven. these Status records may be added to so i wanted soemthing that would do this dynamically.... my cheap and cheerful solution is to prefix the status with the PrtSeq number fso I end up with, for example: "10 - New ", "20 - Approved", "99 - Live". it's just a bit clumsy

    liz

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

    Re: Ordering Xtab Column headings (XP)

    It's more than just a bit clumsy. If you rely on the values to create your column headings, you will only see the headings of the data you have currently. If there are in between columns that don't have any data that day, you won't see them. The column headings mean you will always see the column even if there is no data that day for that value. It is possible to modify the SQL of the query to add column headings later on, but it would normally require code for that purpose.
    Charlotte

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

    Re: Ordering Xtab Column headings (XP)

    As Charlotte says, you can use code to alter the SQL of a query to achieve this result. I have done this before using a table to create and sequence the PIVOT clause.

  6. #6
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Ordering Xtab Column headings (XP)

    Charlotte,

    yes, I don't like it! but what I do is make an outer join to get the "no value" column headings... it does mean that there is one row with rubbish i.e. the no value stuff (in this case it only has "X" in the "cell") crude but expedient!.

    liz

Posting Permissions

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