Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filter unique values (not records) (Access 2003)

    Hi,

    I could not find anything in Search....and i also know that Excel can filter unique values using the Advanced Filter without specifying criteria. But is there any way to filter for unique values in a field in access. I used the queries property setting called Unique Values, but it did not seem to do anything?!!
    Any ideas...tx
    Regards,
    Rudi

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

    Re: Filter unique values (not records) (Access 2003)

    Setting Unique Values to Yes should do what you want, but keep in mind that it means that it looks at all displayed fields to determine whether the combination of values is unique.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter unique values (not records) (Access 2003)

    OK...so that is my problem then...i had about 7 fields in the query. If I need to generate a resulting dynaset consisting of 7 fields, but one of the fields must have unique values, would i have to use two queries, one filtered on the induvidual field, and the other containing the other 6 fields, then create a third query to combine the results? My only problem then is, how can I link the filtered result with the other 6 columns?

    Any ideas?
    Tx
    Regards,
    Rudi

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

    Re: Filter unique values (not records) (Access 2003)

    What do you want to do with the other six fields? If you create a query that returns the unique values of one field, then combine it with a query containing the other fields, you'll have duplicates again.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter unique values (not records) (Access 2003)

    Sorry...unclear.

    Say I have a table of customers. In this list are duplicated phone numbers. Mr Smith is a customer, and Mr Smith Jr is also a customer with the same home phone number. I want a list of filtered data that contains only unique phone numbers, giving me at most only one member of a household and eliminating the rest. How can I filter for that, but keeping all the other details of the filtered data too?

    Tx
    Regards,
    Rudi

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

    Re: Filter unique values (not records) (Access 2003)

    Set Unique Values to No again.
    Select View | Totals or click the Totals button on the toolbar (the one with the capital Sigma)
    Leave the Total option for the phone number field to the default Group By.
    Set theTotal option for the other fields to First (or Last or Min or Max).

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter unique values (not records) (Access 2003)

    SPOT ON!!!...very cool!

    Now (if you dont mind...) can you explain how this works? Whats the logic in this? And is there any subtle difference if I had to use Last, Min or Max?
    Regards,
    Rudi

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

    Re: Filter unique values (not records) (Access 2003)

    If you set Total to First, Access will return the first value (chronologically) in the field for the current phone number. Similar for the others.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter unique values (not records) (Access 2003)

    And I thought there was some hidden technical complexity behind your answer and the first, last, min, max options??!!!
    Thx
    Regards,
    Rudi

Posting Permissions

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