Finding Distinct Records

[MooseMan]

I need what should be a simple query but never the less does not seem to wan to work for me.

I have a table called tblClaims which records claims paid against producers. Each producer could have up to three(3) claims per crop year. One for interest, one for legal, and one for principal.

what I want is to be able to do is to show the total number of producers we paid claims on in a given Crop Year. But when I run the query using distinct and any other possibility I always get producers showing up more than once. One Legal, one principal etc...... If a prodicer has had a cliam paid his name needs to be show up once.

I would appreciate help with this.

Thanks in advance.

 

[scking]

Using DISTINCT is the correct way of displaying only a single row. If the query returns one field from the table and you are using distinct it should work.

SELECT DISTINCT tblClaims.Producer FROM tblClaims;

If, however, you return multiple fields from the table and ANY field is different from any other field it is not distinct and will be returned.

SELECT DISTINCT tblClaims.Producer, tblClaims.ClaimType FROM tblClaims;

Would still return multiple rows because the ClaimType would be prevent the query from identifying the DISTINCT elements. Also, like many do, using the Wildcard to return all fields will make then unique and therefore DISTINCT.

Could one of these be your problem?


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[DamoOz]

try something like this

SELECT DISTINCTROW Producer, count([producer]) as NumOfDups
FROM tblClaims
GROUP BY Producer
HAVING count([claims]) > 1