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

    Sortorder (2007)

    Have two tables and would like to sort on a field from each. Using only two fields. Thought an Union (All) Select would be the route to success and it might be, but can't seem to figure out where the horse and cart go. Want to sort on Name and Deceased. Here is what I have:

    SELECT LastName & ', ' & Firstname AS SortName, Lastname, 1 AS SortOrder
    FROM Members Inner JOIN Attending ON Members.ID = Attending.ID
    UNION ALL SELECT Lastname & ', ' & Firstname as Name,Deceased, 2 as Sortorder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    ORDER BY Sortname, Sortorder;

    This Select creates double records and sorts on Name for the first half which is SortOrder 1.

    How to eliminate the duplication and have two sort orders on two fields?
    Thanks

    Kim

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

    Re: Sortorder (2007)

    What kind of field is Deceased and what role do you want it to play? As it is now, it will be used instead of LastName in the second SELECT part.

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

    Re: Sortorder (2007)

    Thanks for the response. Deceased is a boolean and the others are text. Presently the only part that it is to do is to sort.
    Thanks

    Kim

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

    Re: Sortorder (2007)

    It doesn't make sense to me to combine the text field LastName and the boolean field Deceased in the union query.
    You haven't included the field in the sort order.
    Perhaps we can help you if you try to explain clearly what exactly you want to accomplish.

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

    Re: Sortorder (2007)

    There are 10 fields to include in a select statement including boolean (Deceased) and memo (Comments) fields. Some of the 10 fields have been combined like:

    SELECT Lastname & ', ' & Firstname AS Name, Address, City & ', ' & State & Zip AS CitySTZip, Phone, eMail, Comments, IIF(Deceased,'D','A') AS SortOrder FROM Members INNER JOIN Attending ON Members.ID=Attending.ID
    ORDER BY LastName;

    In addition to the ORDER BY I would like to also include SortOrder so that those Deceased would be placed at the end of the report. The select statement does not recognize SortOrder as a field to sort on.
    Thanks

    Kim

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

    Re: Sortorder (2007)

    There's no need to use a field with IIf - you can sort descending on Deceased itself - since True = -1 and False = 0 sorting descending will place the deceased persons at the end.

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

    Re: Sortorder (2007)

    Coudn't see the forest for the trees. Many thanx for your help.
    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
  •