Results 1 to 4 of 4

Thread: Sorting

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

    Sorting

    Have a db (access) with all the usual profile fields such as last, first, address, city, state, zip and deceased. Some fields will have no data like address, city, state, zip and deceased. Would like to sort by state with the blank fields at the end of the report.

    This is as close as I've been able to get:

    SELECT IIf([MaidenName]='' Or [Maidenname] Is Null,[Lastname] & ', ' & [firstname],[Lastname] & ', ' & [firstname] & ' (' & [Maidenname] & ')') AS Name,
    address, IIF(City<>'',City & ', ' & State & ' ' & Zip,'') AS CityStZip,
    IIf([Deceased],'D','') AS Dead
    FROM Members
    ORDER BY iif(deceased is null, state,'')

    Thanks for the help.
    Thanks

    Kim

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Hi Kim -

    Don't know if this is what you're after, but is based on the fact that, with Yes/No or True/False fields, you can sort ascending or descending directly on that field.

    I read it that you want all the not dead people sorted by state, if they have one. I'm not clear what you want to do with the dead people, but you should be able to adapt this accordingly. I just created a field based on whether or not the state was null, and sorted on that field. You could adapt it further if you thought there might be zero-length rather than null strings for some of the states. But the basic SQL is:

    SELECT Table1.ysnDead, Table1.strState, Table1.strName
    FROM Table1
    ORDER BY Not (IsNull([strState])), Table1.ysnDead DESC , Table1.strState, Table1.strName;

    This sorts first by state, putting the nulls at the bottom of the list. Then by dead-or-alive, putting the live ones above the dead ones, before those with no state listing. Then by state then name within those first groups.

    If I'm on the right track for what you want, but you need a bit more information, let me know and I'll try to clarify/expand on this.

    Cheers

    Alison C

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts
    You're not only on the right track, that is a bulls eye.

    How to Thank?
    Last edited by kim; 2011-05-10 at 21:29.
    Thanks

    Kim

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    The bulls-eye comment is thanks enough!

    Cheers

    Alison

Posting Permissions

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