Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Say we have a table tblParts
    ABC
    ABC
    ABC
    DEF
    DEF

    How to create a query of unique list based upon the above table?
    ABC
    DEF

  2. #2
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Armstrong,
    The unique list can be created by following the steps:
    1. In the Design View, press Totals button;
    2. In Total grid field, choose group by.

    Then the query will create a unique list.

    Armstrong

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Set the Unique Values property of the query to yes. This corresponds with changing the SQL to
    Select Distinct ....

    In Query Design, display the properties box, the click some empty space in the top half of the grid, so the Properties box tells you about the query, rather than one of its fields.
    [attachment=84048:unique.gif]
    Attached Images Attached Images
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,
    Thanks for your help.
    The actual tblParts are rather complex and the PartNo repeats freely. That's exactly where I find your Property sheet won't work in my case.
    The combination of PartID (based on autonumber) and PartNo always render each record unique. The objective here is to create a list of unique PartNo.
    Armstrong

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you just want to create a list of unique PartNos, you shouldn't include PartID in the query, only PartNo. Setting "Unique Values" to Yes will then ensure that each PartNo occurs only once.

    If you want to do more, for example count the number of occurrences of each PartNo, you need a Totals query that groups by PartNo. But that wasn't what you asked in the first post in this topic...

  6. #6
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV,
    I couldn't thank enough for your help. Your guide works brilliantly. Yes, selecting PartNo only and setting Unique Values property to Yes succeed to generate a list of unique PartNos.
    Thanks again.
    Armstrong

Posting Permissions

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