Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Union Query Decending? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following query that works OK

    How do I change it so it sorts by date decending?

    Thanks, John

    <pre>SELECT Format(tblArrivals.dteDateArrival,"mm/dd/yyyy")
    AS Arrival, Count(tblArrivals.lngID)
    AS [Claims] FROM tblArrivals GROUP BY tblArrivals.dteDateArrival
    UNION SELECT "<All Dates>", 1 FROM tblArrivals
    UNION SELECT "<New Date>", 1 FROM tblArrivals
    </pre>


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

    Re: Sort Union Query Decending? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Add the ORDER BY clause at the end, and use the field name as specified in the first query:

    SELECT Format(tblArrivals.dteDateArrival,"mm/dd/yyyy") AS Arrival, Count(tblArrivals.lngID) AS [Claims] FROM tblArrivals GROUP BY tblArrivals.dteDateArrival
    UNION SELECT "<All Dates>", 1 FROM tblArrivals
    UNION SELECT "<New Date>", 1 FROM tblArrivals
    ORDER BY Arrival DESC

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Union Query Decending? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks for the clue, I had to change as follows because it was sorting by month desending

    Was this the best way to accomplish this?

    Thanks, John

    <pre>SELECT Format(tblArrivals.dteDateArrival,"mm/dd/yyyy") AS Arrival,
    Count(tblArrivals.lngID) AS [Claims],
    format(tblArrivals.dteDateArrival,"yyyy/mm/dd") as [SortDate]
    FROM tblArrivals GROUP BY tblArrivals.dteDateArrival
    UNION SELECT "<All Dates>", 1,9
    FROM tblArrivals UNION SELECT "<New Date>", 1,9 as [SortDate]
    FROM tblArrivals
    ORDER BY SortDate DESC
    </pre>


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

    Re: Sort Union Query Decending? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You could also use dteDateArrival as a date (not as a formatted string) as sort field, and provide a dummy date for the other two tables - something like #01/01/9999# if you want them to be sorted at the top, or #01/01/100# if you want them at the bottom. For example:

    SELECT Format(tblArrivals.dteDateArrival,"mm/dd/yyyy") AS Arrival, Count(tblArrivals.lngID) AS Claims, tblArrivals.dteDateArrival AS SortDate
    FROM tblArrivals
    GROUP BY tblArrivals.dteDateArrival
    UNION SELECT "<All Dates>", 1, #01/01/9999#
    FROM tblArrivals
    UNION SELECT "<New Date>", 1, #01/01/9999#
    FROM tblArrivals
    ORDER BY SortDate DESC

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Union Query Decending? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Cool!

Posting Permissions

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