Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help (2000)

    I have a query that has a field - PACounty. In this field are a list of counties. Also, in the list are, besides county names, two names - "Other" and "Out of State". When I use this field in a report, they want "Other" and "Out of State" to appear last in the list. Is there a way to sort this field (PACounty) so that "other" and "out of State" appear last? Thank s for your help.

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

    Re: Query Help (2000)

    Create a table tblCounties with two fields: PACounty (text) and Sort (number, Long Integer). Enter the list of unique counties into the PACounty field, plus "Other" and "Out of State". Set the Sort field to 1 for all counties and to 2 for the other options.

    Add the tblCounties table to the query, joined to the existing table on PACounty. Add the Sort field to the query grid.

    Open the report in design view. Add Sort to the Sorting and Grouping window, and move it to above PACounty if PACounty was already present.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    Is there another way? Can I do this in the query? The county list changes so I would have to make sure the county table contained the correct counties.

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

    Re: Query Help (2000)

    You could add Sort as a calculated column to the query instead of getting it from a table:

    Sort: Not ([PACounty]="Other" Or [PACounty]="Out of State")

    and proceed as in my previous reply.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    I am sorry for being so dense this morning. Where do I indicate 1 for all counties and 2 for other and out of state? I got the part about puting the sort field above the PACounty field in the sorting and grouping window of the report.

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

    Re: Query Help (2000)

    The expression for Sort will evaluate to True = -1 for counties and to False = 0 for "Other" and "Out of State". If you sort on that field, the counties will come before "Other" and "Out of State" because -1 is smaller than 0. The values 1 and 2 don't come into this any more.

Posting Permissions

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