Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query Sort (2002)

    Want to sort by Marriedname then in absence of marriedname sort by lastname something like IIF(Marriedname<>'', Marriedname,Lastname). However in this query it sorts by Lastname then Marriedname:

    SELECT Members.FirstName, Members.LastName, Members.MarriedName, Attending.Attending FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Attending.Attending
    UNION SELECT Members.Spouse, Members.LastName, Members.MarriedName, Attending.Attending FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE (((Members.Spouse)<>'') and Attending.Attending)
    ORDER BY Marriedname, Lastname;

    Query will not let me use the IIF statement I mentioned above. Where to go?

    Thanks

    Kim

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

    Re: Union Query Sort (2002)

    Your SQL statement has "ORDER BY Marriedname, Lastname" but you state that it "sorts by Lastname then Marriedname". <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Maybe you should use a calculated field Nz(MarriedName, LastName). You can add this in the individual queries:

    SELECT Members.FirstName, Members.LastName, Members.MarriedName, Attending.Attending, Nz(MarriedName, LastName) As SortName FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Attending.Attending
    UNION SELECT Members.Spouse, Members.LastName, Members.MarriedName, Attending.Attending, Nz(MarriedName, LastName) FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Members.Spouse Is Not Null and Attending.Attending
    ORDER BY SortName;

    or store the union query without a sort clause, then create a new query based on the union query, add the calculated field there, and sort on it.

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query Sort (2002)

    Thanks for the response, Hans. How does the calculated field Nz(Marriedname, Lastname) as Sortname translate to VB?

    Thanks.

    Kim

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

    Re: Union Query Sort (2002)

    Nz is a VBA function that is specific to Access. You can use it in VBA code, in expressions in queries and in the control source of controls on forms and reports.

Posting Permissions

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