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

    Union select (Access 2007)

    Trying to change the sort order on an union select query . What I want to do is to sort on maiden name for name tags so that both the member maiden name tag and spouse name tag are sequenced together. This query is almost there. It sorts correctly for those member males and females. However, for member female spouse's it sorts on the lastname and not the spouse's maidenname.


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

    Kim

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

    Re: Union select (Access 2007)

    Could you post a small database with some test data? See <post#=401925>post 401925</post#> for instructions.

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

    Re: Union select (Access 2007)

    Thanks for the response. Here is the file.
    Attached Files Attached Files
    Thanks

    Kim

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

    Re: Union select (Access 2007)

    I think you should use IIf(maidenname<>'';Maidenname;Lastname) as the SortName field in the second part too:

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

  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 (Access 2007)

    Hans,

    I knew if I came to the mountain, the answer would be there.
    Thanks

    Kim

Posting Permissions

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