Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Query, Select only one instance (Access 2000)

    I have a user who has a very large database. There are multiple instance of similar information, but none of them are unique in all fields. She only needs certain information, for example LastName and IDNumber so she doesn't need to see information from every record. This is external data imported into Access to run reports, etc. She would like to have only one instance of each LastName and IDNumber appear on the query results. Is there a way of setting a criteria to unique at the field level rather than just at the query/record level? Does that make sense? In other words say John Smith shows up six time in the query result, but she only wants to see it one time. How can she accomplish that?
    Thanks much, Judy Crawford

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

    Re: Query, Select only one instance (Access 2000)

    What do you mean by that? If John Smith occurs 6 times, but some fields for each of these John Smiths are different, there is no way to show John Smith only once AND display those other fields. If you only include the name in the query, you can set the Unique Values property of the query to Yes to suppress duplicates, but this will fail if you include fields that have different values for these Smiths.

    In a report, it is possible: set the Hide Duplicates property of the text boxes containing the name to Yes.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Query, Select only one instance (Access 2000)

    Thanks for your reply. I guess I didn't word that last comment right! I realize that you can't pull from multiple records by seeing the other fields & John Smith only once. What I really was trying to say is let me see smith only once even if its in the database six times. You idea on including only the fields where there are known duplications might work and also the thought of a report suppressing duplicates. Thanks much, Judy
    Thanks much, Judy Crawford

Posting Permissions

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