Add a second count

D

Dan @BCBS

Is it possible to add a second count in a sorted report.
Senerio: Members have different types of Insurance ie: HMO PPO etc.
My report returns a count for every claim per Insurance type. I need to
count the number of members with claims. But, one member may have mulitple
claims, so HMO may show 50 when there were really only 30 different members...

Presently, the report has an Insurance Type Header and Footer. The footer
returns the count with =Count(1).

Is there a way to also count the Distinct Member numbers.

Here is the Query SQL that builds the report:
SELECT DISTINCTROW tblQualityData.ICNNo, tblQualityData.GBULocation,
tblQualityData.CSReceiptDate, tblQualityData.IssueCloseDate,
IIf([IssueCloseDate] Is Null,Null,MonthName(Month([IssueCloseDate]),True)) AS
Months, IIf([issueclosedate] Is Null,Null,[issueclosedate]-[ReceiptDate]) AS
LessThen30Days, TBLPROVIDER.PROVNO, tblSource.Source, TBLPROVIDER.LSTNAM,
TBLPROVIDER.FSTNAM, tblQualityData.ReceiptDate, IIf([csreceiptdate] Is Null
Or [csreceiptdate]>[receiptdate],[receiptdate],[csreceiptdate]) AS StartDate,
tblQualityData.InsuranceType, tblQualityData.MemberNo,
tblMemberInfo.LastName, tblMemberInfo.FirstName, tblReviewers.Reviewer,
tblQualityData.AcknowledgementDate, IIf([issueclosedate] Is
Null,Null,IIf([startdate]<#6/1/2002#,businessdays([startdate],[issueclosedate]),[issueclosedate]-[startdate]))
AS DaysOpen, IIf([acknowledgementdate] Is
Null,0,businessdays([startdate],[acknowledgementdate])) AS AckDays,
tblReviewers.RACF, tblSource.SourceType, tblQualityData.SeverityLevel
FROM ((tblSource INNER JOIN (tblReviewers INNER JOIN (tblMemberInfo INNER
JOIN tblQualityData ON tblMemberInfo.MemberNo = tblQualityData.MemberNo) ON
tblReviewers.RACF = tblQualityData.Reviewer) ON tblSource.Source =
tblQualityData.Source) INNER JOIN tblQualityProvider ON tblQualityData.ICNNo
= tblQualityProvider.ICNNo) LEFT JOIN TBLPROVIDER ON
tblQualityProvider.ProvNo = TBLPROVIDER.PROVNO
WHERE (((tblQualityData.IssueCloseDate) Between
[forms]![f_KeyIndicators].[txtstart] And [forms]![f_KeyIndicators].[txtend])
AND ((IIf([issueclosedate] Is
Null,Null,[issueclosedate]-[ReceiptDate]))<=30));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error 3075 4

Top