Query, Select only one instance (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote