1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How sort by 2nd field (date) without grouping when date changes?

Discussion in 'Business Applications' started by JimSha, Dec 2, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. JimSha

    JimSha Thread Starter

    Joined:
    Dec 2, 2005
    Messages:
    3
    Help! This should be easy but I can't get it to work.

    I have a report that must group by Trip# and sort by ascending activity date all of the various activities that took place on multiple days of the trip.
    Further, the trip with the earliest date must come first regardless of the Trip#.
    I've accomplished getting the earliest starting date trip first and grouping its detail with a unique string made up of the earliest date of each trip formatted to YYYYMMDD+Trip#. This prints a trip total and works fine.

    But the daily activity for the trip is out of date sequence.
    Adding a Date field to the 'Sorting and Grouping' insists on breaking on and printing a sub-total for each of the trips activity dates.
    Without the sorting & grouping by date the trip records are out of sequence even though the data source query is sorted ascending by activity date.

    [​IMG] (see attachments that show the above)

    The only solution I can think of is to create a sub-report sorted by activity date.

    Does anyone have any better ideas?

    Jim Shannon
    JES Computer Systems
    Access Developer.
     

    Attached Files:

  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    Jim, What is the #name on the right which is causing the breaks?
    Any chance of posting a zipped copy with minimal data to play with as I am sure it possible to do just what you want.
    Are you printing One Trip per page?
    Have you tried adding the Trip number to the sort as the first sort item?
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    Jim, I have just created a report based on the first few dates of your snapshot and it sorts perfectly by trip and date.
    I just used the Report Wizard and it's "Do you want to sort" and used the trip as first sort field and the date as second sort field.
    What am I missing?
     
  4. JimSha

    JimSha Thread Starter

    Joined:
    Dec 2, 2005
    Messages:
    3
    The data is deceptive.
    The sort order is NOT trip and date. That's what I first gave the customer.
    And it can't just be sorted by date as each trip contains may dates which may overlap other trips.
    When it prints, all dates for each trip must stay with the trip.

    A preliminary sort for the earliest date within each trip must be done first. Then the earliest dated trip which is probably NOT the lowest trip number is printed and its activity sorted in ascending order.

    I'm certain this can be done but finally gave up and did a sub-report to solve the problem.

    If you can figure it out I'd love to know the trick but it's academic at this point.

    Thanks for your help though.

    Jim Shannon
    JES Computer Systems
    [email protected]
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    Jim, I am intrigued by this problem, I know it is only academic but it is bugging me now.
    I reproduced your sort code as that is the way I would have done it in BASIC, which I used to program in. It should work the way you want, it does so with my limited data.
    What I do not understand is what produced your "breaks" in the printout with #Name causing the breaks, did some kind of grouping or header get introduced without you realising?
    Is there any chance I could have the table with the trips and dates in to work it out?
     
  6. JimSha

    JimSha Thread Starter

    Joined:
    Dec 2, 2005
    Messages:
    3
    I finally figured out what I had missed.
    The job was: filter by aircraft and PURCHASE CATEGORY, sort ascending by trip date and group by trip. So the flaw was my giving them Aircraft + Date ascending when they wanted Aircraft + category + date ascending.

    When sorted by aircraft + date and printed by aircraft, PURCHASE CATEGORY and date the customer wanted the earliest date within a trip and category to print first.
    I was taking the earliest date within the aircraft (EG Planning - call it date A) and using it to order the trips for each of the categories (EG catering, landing fees, per diem all of which occurred with later dates).
    So, when it printed the category transaction date for a category other than the category with the earliest transaction date (date A) but in the sequence of the date B,C,D... I was winding up with the earliest trip per date A but the date of the category dates were later causing the confusion.
    Trip 1 Planing 01/01/05, Catering 01/05/05, Landing 01/06/05, Per diem 01/08/05
    Trip 2 Planing 12/28/04, Catering 01/06/05, Landing 01/07/05, Per diem 01/09/05

    When the report printed, Trip 2 printed first because its earliest date was 12/28/04
    But when the category Catering printed it's earliest date for trip2 was 01/06/05 and Trip1's earliest catering date was 01/05/05

    Trip# Category date
    2 catering 01/06/05
    1 catering 01/05/05

    So, again, the flaw was my giving them Aircraft + Date ascending when they wanted Aircraft + category + date ascending.

    Thank you for your help and persistence.

    Jim
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/421778

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice