Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Union Select (2003)

    Sometime ago this forum helped me with a union select for name tags. It works great. Now I want to sort the data so that if there is a spouse the two names will print consecutively. This select statement prints all spouse name tags, then classmate name tag.

    This is from VB6.

    "SELECT Members.FirstName, Members.LastName, MarriedName," & _
    " Classyear, Members.ClassPhoto,IIF(Marriedname<>'', Marriedname,Lastname) as Sortname" & _
    " FROM Members INNER JOIN Attending ON Members.ID = Attending.ID" & _
    " Where Attending.Attending" & _
    " UNION ALL SELECT Spouse," & _
    " IIF(MarriedName = '',Lastname, Marriedname), '(Spouse)'," & _
    " IIF(members.spouse = '',Members.Classyear, '')," & _
    " IIF(Marriedname = '(Spouse)','',''), IIF(Marriedname='', Lastname,Marriedname)" & _
    " FROM Members INNER JOIN Attending ON Members.ID = Attending.ID" & _
    " WHERE Spouse <> '' AND Attending.Attending" & _
    " ORDER BY Classyear, Sortname"

    I've worked on this for several hours and can not see the solution. Any help is great.
    Thanks

    Kim

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

    Re: Union Select (2003)

    Can you provide a small sample of the data? (names can be faked)

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

    Re: Union Select (2003)

    Sorry, I don't understand this setup - what are the fields? It would be easier if you attached a small database - see <post#=401925>post 401925</post#> for instructions.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (2003)

    <table border=1><td>kim</td><td>Mary Ann</td><td>Peterson</td><td>Peterson</td><td></td><td>(Spouse)</td></table>
    <table border=1><td>Betty</td><td>Sam</td><td>Smith</td><td>Jones</td><td>Jones</td><td>(Spouse)</td></table>
    Thanks

    Kim

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (2003)

    Here's the a portion of the db.
    Thanks

    Kim

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

    Re: Union Select (2003)

    Thanks. Try this:

    SELECT FirstName, LastName, MarriedName, ClassYear, ClassPhoto,ClassYear AS OrderYear, LastName AS Sortname, 1 AS SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Attending
    UNION ALL
    SELECT Spouse, IIF(MarriedName = '',Lastname, MarriedName), '(Spouse)', IIF(Spouse = '',ClassYear,''), IIF(MarriedName = '(Spouse)','',''), ClassYear, LastName, 2 As SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Spouse <> '' AND Attending
    ORDER BY OrderYear, SortName, SortOrder

    See attached version (enter anything for ClassPhoto when trying the query).

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (2003)

    Hans,

    That does the job. Thanks for the help.
    Thanks

    Kim

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (2003)

    Hans,

    Using the select statement:

    SELECT FirstName, LastName, MarriedName, ClassYear, ClassPhoto,ClassYear AS OrderYear, LastName AS Sortname, 1 AS SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Attending
    UNION ALL SELECT Spouse, IIF(MarriedName = '',Lastname, MarriedName), '(Spouse)', IIF(Spouse = '',ClassYear,''), IIF(MarriedName = '(Spouse)','',''), ClassYear, LastName, 2 As SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Spouse <> '' AND Attending
    ORDER BY OrderYear, SortName, SortOrder

    as you suggested sorts on Lastname. Thought it would not be difficult to change the order to sort on Marriedname. Wrong. If Lastname AS Sortname is changed to Marriedname AS Sortname, a mess is created of no particular order. My knowledge about union select queries is limited. What needs to be changed to accomplish this new sort order?
    Thanks

    Kim

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

    Re: Union Select (2003)

    You must use MarriedName in both SELECT statements:

    SELECT FirstName, LastName, MarriedName, ClassYear, ClassPhoto,ClassYear AS OrderYear, MarriedName AS Sortname, 1 AS SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Attending
    UNION ALL SELECT Spouse, IIF(MarriedName = '',Lastname, MarriedName), '(Spouse)', IIF(Spouse = '',ClassYear,''), IIF(MarriedName = '(Spouse)','',''), ClassYear, MarriedName, 2 As SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Spouse <> '' AND Attending
    ORDER BY OrderYear, SortName, SortOrder;

  10. #10
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (2003)

    Thanks, Hans.

    The sort order in this table is different than expected after making the changes.
    Thanks

    Kim

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

    Re: Union Select (2003)

    What did you expect? In other words, in which order do you want to return the records?

  12. #12
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (2003)

    If sorted on marriedname then it would seem to me that mary smith able would come before kim peterson. In query 1 that is not the case. Also Kim and Mary Peterson do not have a sortname. Could that be the reason?
    Thanks

    Kim

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

    Re: Union Select (2003)

    The MarriedName field in the record for Kim and Mary Ann is blank. Should LastName be used if MarriedName is blank? If so, try

    SELECT FirstName, LastName, MarriedName, ClassYear, ClassPhoto,ClassYear AS OrderYear, Nz(MarriedName,Lastname) AS Sortname, 1 AS SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Attending
    UNION ALL SELECT Spouse, Nz(MarriedName,Lastname), '(Spouse)', IIF(Spouse = '',ClassYear,''), IIF(MarriedName = '(Spouse)','',''), ClassYear, Nz(MarriedName,Lastname), 2 As SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Spouse <> '' AND Attending
    ORDER BY OrderYear, SortName, SortOrder;

  14. #14
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (2003)

    Hans,

    Both now work as intended. Sorting either by Lastname or Marriedname. What I am bringing away from this exercise is that the sort order must be stated in both parts of the UNION SELECT. Does it matter the order that each occurs within their respective part. That is, If the first sort order is #2 does the sort order in the second part also have to be #2?

    Thanks very much for your patience and help.

    Kim
    Thanks

    Kim

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

    Re: Union Select (2003)

    A union query has only one ORDER BY clause - at the end of the SQL statement.
    But the fields on which you want to sort must be present in all SELECT clauses, and in the same order.
    The union query will first combine the records from all of the SELECT clauses into one long list, then apply the ORDER BY clause.

Page 1 of 2 12 LastLast

Posting Permissions

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