Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping and Sorting (A2K SP3)

    I currently have rptFolio whose Sorting and Groping is set to
    RoomNumber Ascending
    CombinedName Ascending
    RentEndDate Ascending

    The Report breaks on the Room Number

    For Example if there are 3 records
    Room # Name Record number Rent Begin Date
    143 Amos, Mike 1 04/28/05
    154 Amos, Mike 2 05/12/05
    154 Amos, Mike 3 05/14/05

    Currently it prints
    143 Record 1
    Room Total

    154 Record 2
    154 Record 3
    Room Total

    Grand Total

    Our operations people want it to print the most recent transaction first, grouped by Room Number

    154 Record 2
    154 Record 3
    Room total

    143 Record 1
    Room total

    Grand Total

    Any suggesting?

    Tom

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping and Sorting (A2K SP3)

    Tom
    Why can't you sort descending by date first, and then do your additional grouping/sorting?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Grouping and Sorting (A2K SP3)

    Create a Totals query based on your table/query, with two fields:
    Room number (Group By)
    Rent Begin Date (Max or Min, depending on what exactly you want to use to determine the sort order in the report)
    Save this query.

    Next, create a new query based on the original table/query and the Totals query, joined on the Room number field.
    Add all fields (or *) from the original table/query, plus the MaxOf... or MinOf... field from the Totals query.
    Save this query.

    Open the report in design view.
    Set the Record Source to the name of the second new query.
    Enter the MaxOf... or MinOf... field in the Sorting and Grouping window, and set it to sort Descending.
    Move this row up to just above the grouping level for Room number.
    The report should now sort the way you want.

    Note: this reply does not in any way imply that the Lounge approves of groping in hotel rooms. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping and Sorting (A2K SP3)

    Many thanks for your assistance. The use of 3 queries makes it sort as desired.


    Tom

Posting Permissions

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