What is the best way to remove duplicates

[jereece]

I have a query linked to a main frame database. One of the fields is [significance] and gets a number 1-20. Usually when this data is entered, it gets multiple significance numbers. This causes my query to return separate records for each significance number. For example if case number 123 is given significance codes 1, 5 and 12, then my query returns 3 records.

I need a query that will show all records one time that have a significance code other than 12. This would be easy if there were not duplicate entries for the same case number because I could simply say "Not 12". So in the example above, my query returns 2 records showing significance codes 1 and 5. But I don't want to see the record for case number 123 because it also has a 12 significance code.

So what is the best way to handle this?

I appreciate the help.

Jim

 

[Dennisk]

use a total query and group on case number

or if you only require a list of case numbers then set the unique property of the query to yes ie use the DISTINCT keyword.