Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Sorting (XP)

    I am new to Access (but not new to relational databases).

    I am trying to build a report based on a query, however, the query is sorted on columns that are not being returned by the query. The query sorting works fine, but when I try to create a report from that query the sort order appears to be overridden by the report in some manner. Is there anyway to retain the sort order returned by the query?

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

    Re: Report Sorting (XP)

    In the design view of the report, select the menu View | Sort and Grouping.
    Enter there your sorting field(s) or remove each line to have the query sorting.
    Francois

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Sorting (XP)

    This one gets all Access newbies (myself included). Access reports totally ignore any sorting you have in the underlying query; so don't even bother to put anything there. They strictly use the Sorting And Grouping option within the report, selectable when in design mode for the report by a button on the toolbar, or by selecting "View" from the menu bar.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Feb 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sorting (XP)

    I think that my twist here is that I am using Grouping, but I want to override the natural sort order of the Group columns, that's why I am actually sorting on another column that is NOT being displayed by the Query, and in turn by the report. The sort order has been customized by a separate field. Therefore, this field is not being seen by the Report and is not available as a sort column.

    Do I need to return this column from the query and then hide it on the Report? If so, how? Or is there another approach?

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Sorting (XP)

    You can't "override" the sort order done by the grouping. Grouping does sorting. What you need to do is make sure your query shows that field that you want to sort on, then make this field the first field in your sorting/grouping list. It won't show on the report.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    Feb 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sorting (XP)

    All right, then let me restate the problem with an example. Suppose you have a database of issues and want to create a report where the issues are group by priority in the order of High, Medium, then Low. An alphabetic sort wouldn't work ("Low" would sort before "Medium").

    How do you do that?

    Of course you could change to numeric priorities, or add a "1". "2" and "3' prefix to the priorities, but there are other reasons why that is not optimal.

    Any ideas?

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Sorting (XP)

    I'd add an expression to my query like this:
    PriorityNo: =Switch("High",1,"Medium",2,"Low",3)

    Then sort it ascending. Then in your report, PriorityNo would be the first field in your sorting/grouping list.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    New Lounger
    Join Date
    Feb 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sorting (XP)

    Right, I already have a solution for getting the correct sort order (I used a join with a table that contains the sort orders I want), but I don't want the sort order number to appear on the report, I want the word to appear without the associated number.

    Is that possible?

    Two additional notes:

    First, I don't think the syntax you provided is quite correct. I had to make modifications to get the query to run at all, so it's possible that I am missing the point.

    Second, where can I find a list of these functions? I can't find them anywhere in the Access Help files.

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

    Re: Report Sorting (XP)

    Add the sort number to the sort window.
    If you don't want a header, add the "word" field to the detail section.
    If you want a header, create it by setting yes to group header in the sort window.
    In the header you can put the field with the "word".
    What is displayed in the header do not affect the sorting.
    Francois

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Sorting (XP)

    Oops. slight error in use of switch. Let's assume you already have a Priority field (with values, "High", etc.). You want that field to show in the query. Additionally, you will create another field which contains this expression:

    PriorityNo: Switch(Priority="High",1,Priority="Medium",2,Prior ity="Low",3)

    In your report's sorting/grouping, you want to specify "PriorityNo" as your first entry. You can then put a textbox anywhere (in header or detail) that has "Priority" as its controlsource.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Report Sorting (XP)

    JFord,
    You can't find ANY real help in the Access Help files. This made me nuts a few years ago when I upgraded since I was used to everything being in one place. What I've discovered is that you actually need to be in the code area to get any real help. I always keep a blank, dummy form open just so I can open it in Design view, and click on the "Code" button. From there you can access the VB Help file. I like the "dummy" form because it doubles as a place to test code that you're just learning.

Posting Permissions

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