Results 1 to 9 of 9
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    I don't understand what you are doing in this query - you are putting numeric and text data in the same field.

    But anyhow, you cannot sort the individual queries contributing to the union query; you can specify an ORDER BY clause at the end of the SQL to set the overall sort order:

    SELECT ...
    UNION SELECT ...
    ORDER BY [Batch No] DESC

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

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

    I
    Attached Files Attached Files

  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: Help With Union Query Sort? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Edited by HansV to break extremely long lines that caused horizontal scrolling.

    Hans

    What I

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

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

    As I wrote before, I don't understand this part:

    SELECT 'New Batch' as [Batch No], '' as [Start], '' as [End], '' as [Reference], '' as [Claims], '' as [OK]
    from tblSumServe
    GROUP BY tblSumServe.strBatchNo

    You claim to be selecting from a table and grouping by a field, but you only specify literal strings 'New Batch' and '' as values, not field names. What is the purpose?

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

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

    That part just joins a non-existent record to the query, like adding an "All" selection to a combobox list.
    Charlotte

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

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

    OK, but why the Group By?

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

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

    John,

    The Group By belongs in the first Select query, not in the second. Remove the fictitious record union and go back to a SELECT query and get the GroupBy straight. Then do a UNION with the fictitious record and then do the ORDER BY. I think you're going to have to use <code>GROUP BY tblSumServe.strBatchNo AS [Batch No]</code> to get it to work properly in your totals query.
    Charlotte

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

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

    Charlotte already found the problem - the Group By is in the wrong place.

    SELECT tblSumServe.strBatchNo AS [Batch No], Min(tblSumServe.dteSUMDATE) AS Start, Max(tblSumServe.dteSUMDATE) AS [End], First(tblSumServe.strREF) AS Reference, Count(tblSumServe.idsKeyOfSumServe) AS Claims, First(tblSumServe.strOK) AS OK
    FROM tblSumServe
    WHERE (((tblSumServe.strREF)="DENTAL") AND ((tblSumServe.strBatchNo) Is Not Null) AND ((tblSumServe.strOK)<>"Y")) OR (((tblSumServe.strOK) Is Null))
    GROUP BY strBatchNo
    UNION SELECT 'New Batch' as [Batch No], '' as [Start], '' as [End], '' as [Reference], '' as [Claims], '' as [OK]
    FROM tblSumServe
    ORDER BY [Batch No] DESC;

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

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

    Charlotte & Hans

    Perfect!

    Thanks for sticking with me on this one.

    John

Posting Permissions

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