total count

[JRough]

I have this query which counts all the claims for each member but I
want the total number of claims so I can double check the number of
records submitted is right. How do I get the total count?tnx
SELECT [2007_SUBMISSION_HISTORY].MembID,
Count([2007_DATA_SOURCE].CLAIMNO) AS CountOfCLAIMNO
FROM 2007_SUBMISSION_HISTORY INNER JOIN 2007_DATA_SOURCE ON
[2007_SUBMISSION_HISTORY].MembID = [2007_DATA_SOURCE].MEMBID
GROUP BY [2007_SUBMISSION_HISTORY].MembID;

 

[JRough]

P.S. I tried this as an alternate way to do it but it didn't compile,
I was thinking I could just count the number of records in the query.
thanks again.

SELECT [2007_DATA_SOURCE].CLAIMNO
FROM 2007_DATA_SOURCE
HAVING ((([2007_DATA_SOURCE].CLAIMNO) In (select MEMBID from
[2007_I_05_GET_SUBMISSION_MEMBERS])));

 

[John Spencer]

To get the total count you drop the field you are grouping on

SELECT Count([2007_DATA_SOURCE].CLAIMNO) AS CountOfCLAIMNO
FROM 2007_SUBMISSION_HISTORY INNER JOIN 2007_DATA_SOURCE
ON [2007_SUBMISSION_HISTORY].MembID = [2007_DATA_SOURCE].MEMBID

If you want both counts in one query, then life is a bit more difficult. You
will have to use a subquery in the select clause

SELECT [2007_SUBMISSION_HISTORY].MembID,
Count([2007_DATA_SOURCE].CLAIMNO) AS CountOfCLAIMNO,
(
SELECT Count([2007_DATA_SOURCE].CLAIMNO) AS CountOfCLAIMNO
FROM 2007_SUBMISSION_HISTORY INNER JOIN 2007_DATA_SOURCE
ON [2007_SUBMISSION_HISTORY].MembID = [2007_DATA_SOURCE].MEMBID) as TotalCount
FROM 2007_SUBMISSION_HISTORY INNER JOIN 2007_DATA_SOURCE ON
[2007_SUBMISSION_HISTORY].MembID = [2007_DATA_SOURCE].MEMBID
GROUP BY [2007_SUBMISSION_HISTORY].MembID;

Or use a union query
SELECT [2007_SUBMISSION_HISTORY].MembID,
Count([2007_DATA_SOURCE].CLAIMNO) AS CountOfCLAIMNO,
FROM 2007_SUBMISSION_HISTORY INNER JOIN 2007_DATA_SOURCE ON
[2007_SUBMISSION_HISTORY].MembID = [2007_DATA_SOURCE].MEMBID
GROUP BY [2007_SUBMISSION_HISTORY].MembID
UNION ALL
SELECT "TOTAL COUNT"
, Count([2007_DATA_SOURCE].CLAIMNO) AS TCount
FROM 2007_SUBMISSION_HISTORY INNER JOIN 2007_DATA_SOURCE
ON [2007_SUBMISSION_HISTORY].MembID = [2007_DATA_SOURCE].MEMBID



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County