Count Unique in One to Many

[GregD]

I'm joining 2 tables that have a one to many relationship. I have a field named ClaimNo that I join on. The criteria for selecting records is on 2 fields on the many side. I only want to get a count of the unique ClaimNo. However, because on the many table I return multiples of claim no the count is too high.

If I don't use an aggregate function and call SELECT DISTINCT, like below, I get the correct number of unique claimno because claimno is the only output field.

SELECT DISTINCT CLAIM_MASTERS.CLAIMNO
FROM CLAIM_MASTERS INNER JOIN CLAIM_DETAILS ON CLAIM_MASTERS.CLAIMNO = CLAIM_DETAILS.CLAIMNO
WHERE (((CLAIM_DETAILS.ALLOWED)>0) AND ((CLAIM_DETAILS.NET)>0));

What I want though, is just a single record with the count. If I use Count(), like below, the DISTINCT is ignored and the query counts all of the records on the many side.

SELECT DISTINCT Count(CLAIM_MASTERS.CLAIMNO) AS CLAIMCount
FROM CLAIM_MASTERS INNER JOIN CLAIM_DETAILS ON CLAIM_MASTERS.CLAIMNO = CLAIM_DETAILS.CLAIMNO
WHERE (((CLAIM_DETAILS.ALLOWED)>0) AND ((CLAIM_DETAILS.NET)>0));

So what I do now call the first query from a second query and then perfrom the Count() to get a single record with the count.

The question is, can I create one query that will give me a count of unique claimno.

Thanks,

Greg

 

[HiTechCoach]

I would use a sub query.

Aggregation: Counts and totals

Instead of creating a query into another query, you can summarize data with a subquery.

This example works with Northwind, to show how many distinct clients bought each product:

SELECT Products.ProductID, Products.ProductName, Count(Q.CustomerID) AS HowManyCustomers
FROM
 (SELECT DISTINCT ProductID, CustomerID
 FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) AS Q
INNER JOIN Products ON Q.ProductID = Products.ProductID
GROUP BY Products.ProductID, Products.ProductName;
Points to note:

• The subquery is in the FROM clause, where it easily replaces another saved query.
• The subquery in the FROM clause can return multiple fields.
• The entire subquery is aliased (as Q in this example), so the main query can refer to (and aggregate) its fields.
• Requires Access 2000 or later.

 

[GregD]

Thanks, that was very helpful and I almost have it. I can't seem to get the alias for the subquery in the right place.

When I use...

SELECT  Count(Q.CLAIMNO) AS ClaimCount
FROM (SELECT DISTINCT CLAIM_MASTERS.CLAIMNO
FROM MASTERS INNER JOIN CLAIM_DETAILS ON CLAIM_MASTERS.CLAIMNO = CLAIM_DETAILS.CLAIMNO 
WHERE (((CLAIM_DETAILS.ALLOWED)>0) AND ((CLAIM_DETAILS.NET)>0)));

I'm prompted for [Q], but I do get the correct count. Obviously, Q is not referenced in the sub-query.

When I try.... (scroll to the right and you will see the 'AS Q')

SELECT  Count(Q.CLAIMNO) AS ClaimCount
FROM (SELECT DISTINCT CLAIM_MASTERS.CLAIMNO
FROM MASTERS INNER JOIN CLAIM_DETAILS ON CLAIM_MASTERS.CLAIMNO = CLAIM_DETAILS.CLAIMNO AS Q
WHERE (((CLAIM_DETAILS.ALLOWED)>0) AND ((CLAIM_DETAILS.NET)>0)));

The SQL parser tells me there is a syntax error. What am I missing?

Greg

 

[MSAccessRookie]

SELECT  Count(Q.CLAIMNO) AS ClaimCount
FROM (SELECT DISTINCT CLAIM_MASTERS.CLAIMNO
FROM MASTERS INNER JOIN CLAIM_DETAILS ON CLAIM_MASTERS.CLAIMNO = CLAIM_DETAILS.CLAIMNO 
WHERE (((CLAIM_DETAILS.ALLOWED)>0) AND ((CLAIM_DETAILS.NET)>0)) [B][COLOR=red]AS Q[/COLOR][/B]);

Try the code as displayed above. The advice that the HiTechCoach gave you is correct, but I think that you have placed the "As Q" in the wrong place.

 

[GregD]

SELECT  Count(Q.CLAIMNO) AS ClaimCount
FROM (SELECT DISTINCT CLAIM_MASTERS.CLAIMNO
FROM MASTERS INNER JOIN CLAIM_DETAILS ON CLAIM_MASTERS.CLAIMNO = CLAIM_DETAILS.CLAIMNO 
WHERE (((CLAIM_DETAILS.ALLOWED)>0) AND ((CLAIM_DETAILS.NET)>0)) [B][COLOR=red]AS Q[/COLOR][/B]);

Try the code as displayed above. The advice that the HiTechCoach gave you is correct, but I think that you have placed the "As Q" in the wrong place.

That got it, thanks.

 

[MSAccessRookie]

That got it, thanks.

As a point of reference, I copied the code and reformatted it to match up all of the "(" and ")". Once I found the ")" that closed the SubQuery, I added the As Q.

 

[GregD]

As a point of reference, I copied the code and reformatted it to match up all of the "(" and ")". Once I found the ")" that closed the SubQuery, I added the As Q.

Yes, I think I had to play around with those as well. Regardless, I got it to work. That is the important thing.

Thanks.