Distinct

[sislman]

Hi,

I have a query with in excess of 20 fields. One of the fields is [POLICY NUMBER]. I want to use DISTINCT to only show rows of data where the Policy number is unique.

If I put DISTINCT after SELECT the query will only include results where the combination of values from all the fields are unique and I dont want this.

How would I go about this.

Any help would be appreciated.

Regards

Matt

 

[cuttsy]

I want to use DISTINCT to only show rows of data where the Policy number is unique.

By this do you mean that you want to ommit all of the records that have duplicate Policy Numbers. For example.

YourTable(ID, PolicyNumber)

1, 2222
2, 2222
3, 2223
4, 2224
5, 2225
6, 2225

Your query results:

3
4

If that is correct then this should do the trick:

SELECT YourTable.*
FROM YourTable
WHERE YourTable.PolicyNumber IN 
       (SELECT YourTable.PolicyNumber
        FROM YourTable
        GROUP BY YourTable.PolicyNumber
        HAVING Count(YourTable.PolicyNumber)=1)

 

[sislman]

Hi,

Basically I want to show each policy number once, not totally disgard the data if there is a duplicate.

I just dont want to show rows with duplicate policy numbers.

Regards

Matt

 

[cuttsy]

Ok well how do you decide which record to show if 2 records have duplicate policy numbers?

For Example

YourTable(ID, EmployeeNumber, PolicyNumber)

1, 76, 2222
2, 82, 2222
3, 43, 2222
4, 22, 2223

What would you want your query results to look like?

You say you only want one occurence of each policy number, but which occurence?

 

[sislman]

Hi,

I see what you mean, however, the reports that I will be running from the query will be most counting records Iso I dont think it really matters what policy number I choose to show.

Regards

Matt

 

[cuttsy]

I don't think there is anything else I can suggest without knowing your table structure and the results you are trying to arrive at.

 

[boblarson]

If you are trying to do this for a report, you can set the grouping level to the policy number so then you can show the policy number once in the group header and the other information below in the details. That way you don't need to do it in the query, but use the power of the report to do it.

 

[grnzbra]

So you are interrested in a count. Are you looking for one number telling you how many different policy numbers there are or are you looking for a count of how many records there are for each policy number?

 

[boblarson]

Also, if you are just wanting a list of policy numbers to count then use a query and put in only the policy number and then click the group button to group and that will give you the list of policy numbers (unduplicated).