Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting a query (Access 97)

    I rarely use Access, so I'm unfamiliar with some of its parts. I have an Access query with >600 addresses that I need to make mailing labels for, but I only want the odd house numbers. And yes, HouseNumber is a separate field. What is the criteria that does that?

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

    Re: Sorting a query (Access 97)

    Assuming that HouseNumber is a number field:
    - Enter the following expression in the first empty column in the query grid:

    [HouseNumber] Mod 2

    - Clear the Show check box for this column
    - Enter 1 in the Criteria row.

    The Mod 2 operation results in the remainder of HouseNumber after division by 2. This is 0 for even numbers and 1 for odd numbers, so we set the criteria to 1 to select only the odd numbers.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a query (Access 97)

    I see where the new column [Expr1: [Street_Number] Mod 2] has the ones and zeros corresponding to even and odd numbers, but when I run the query with [Expr1: [Street_Number] Mod 2=1] in the criteria for the [street number] it's still showing both even and odd. What am I missing?

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

    Re: Sorting a query (Access 97)

    It should look like this (see attachment).

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a query (Access 97)

    It looks just like that but still give both evens and odds... but in the last 15 minutes we just changed our criteria. Now what we want to do is pull a random sample of 182 records out of a query of 623 records. We're sending flyers for a community meeting but don't have the resources to blanket this particular area so we're picking out a random sample and asking them to tell their neighbors. And of course I have no clue as to how to do this either. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Thanks in advance for your help; it's always appreciated!

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

    Re: Sorting a query (Access 97)

    OK, remove or clear the column with Mod 2.
    Instead, enter this expression:

    Dummy: Rnd([SomeField])

    where SomeField can be any number field in the table (a Date/Time field will do to, since dates are stored as numbers).
    Set the Sort Order for this column to Descending.
    Click in an empty part of the upper half of the query window.
    Activate the Properties window (its title should be Query Properties).
    Set the Top Values property to 182 (you can't select it from the dropdown list, but you can type it in)

    Each time you run the query, it'll return a different set of 182 records. If you want to "fix" the random selection, you can turn the query into a make-table query and create a temporary table.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a query (Access 97)

    Works like a charm! Thanks, as always.

Posting Permissions

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