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));
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));