Count Problem

[cvaccess]

I have a table that contains duplicate values. I want to count one instance of each. For example, the table pend_list has claim_no field with data:

claim_no
2215176239
2215176239
2215176240
2215176241
2215176245
2215176247
2215176247

The count of the claim_no should be 5 but I keep on getting 7 since it is counting the dup claim_no. How can I change this query to count appropriately?

SELECT DISTINCTROW Count(pend_list.CLAIM_NO) AS ClaimCount
FROM pend_list;

Thanks.

 

[antomack]

Simplest way would be to do a group query first and then count the CLAIM_NO's in this query to get your count of number of claims.

 

[cvaccess]

sum problem

Is this what you are talking about?

SELECT DISTINCTROW Count(pend_list.CLAIM_NO) AS ClaimCount
FROM pend_list
GROUP BY pend_list.claim_no;

This lists them out but does not give me a total count. I noticed on the bottom toolbar it gave the correct records but that is not part of the output from the query.

Please help.

Thanks.

 

[Hayley Baxter]

What about hiding the duplicates in the query won't that work for you?

 

[cvaccess]

sum problem

Oh, this table is huge. I would have to manually go through them. Also, the data on one of the fields is different and is needed for another query. Any other suggestions?

Thanks.

 

[Hayley Baxter]

You can hide the duplicates by going into the query properties and selecting unique records = yes then perform your count as normal. I don't know if that's what you're looking for but might be worth a try

Hay

 

[cvaccess]

sum problem

Unique records was set to Yes. I even tried Unique values= Yes a different time and both changes did not work.

Thanks. Any other ideas?

 

[Hayley Baxter]

hmm not sure but you can send it to me if you want and I'll have a look see what I can get to work.

hayley.baxter@bskyb.com

Hay

 

[antomack]

Do it as two queries as follows

Query1
SELECT DISTINCTROW pend_list.CLAIM_NO FROM pend_list GROUP BY pend_list.claim_no;

Query2
SELECT Count(Query1.CLAIM_NO) AS ClaimCount
FROM Query1;

 

[cvaccess]

Antomack,

I have never really done multiple queries before. Are these separate or one combined query (UNION)? I am trying to do it separate but it is not working. How exactly do I do this?

Thanks.

 

[Hayley Baxter]

I've sent your sample back, hope this is what you're looking for.

Hay