Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We have a table including the following fields, from which we wish to generate a mailing list, using criteria on the date and type fields.

    Order number
    Order date
    Order type
    Client number

    When we create an Access 2007 query, some clients are listed more than once because they meet more than one criterion (e.g., they placed more than one order within the specified date range). How can we suppress duplicate clients from the query output? We tried setting the "Unique values" parameter to "Yes" on the client number field, but it did not work.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    There are two ways to do this.
    • Switch to SQL view and add Distinct after Select to the SQL
    • In the query grid, set the Unique Values property to Yes. (as you have done.)
    This only works if the values returned by the query are really identical. So if you include in the query the order number, then the two records from the one client are not identical because the order numbers will be different.
    You can use fields for query criteria without showing them in the query results. Remove the Show tick.
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. We found that we could also do this by using totals and grouping by client number, or just unticking the "Show" option for the other fields.

Posting Permissions

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