Reports return results after 15 mins!

Z

zufie

Some of my Reports return results after 15 mins!

What can I do to make thses reports return results faster?

Include an Index?

Any ideas?

Thanks!

John
 
P

Piet Linden

Some of my Reports return results after 15 mins!

What can I do to make thses reports return results faster?

Include an Index?

Any ideas?

Thanks!

John
Click to expand...

Indexing will help a lot. optimizing your queries wouldn't hurt
either. Like replacing IN(list) with joins etc.
 
N

Noëlla Gabriël

Hi,

don't know if you have, but using subqueries always slows down the process.
Sending your sql statement would help.
 
P

Piet Linden

Hi,

don't know if you have, but using subqueries always slows down the process.  
Sending your sql statement would help.
Click to expand...

No, never mind... we like playing pin the tail on the donkey...
 
Z

zufie

Some of myReportsreturnresultsafter15mins!

What can I do to make thsesreportsreturnresultsfaster?

Include an Index?

Any ideas?

Thanks!

John
Click to expand...

Sorry.
The first report is a QA report that returns every 10th report for the
date range entered. The code of the query behind the report follows:

SELECT [IBCCP Referral].[Caller ID], [IBCCP Referral].CallTaker,
[IBCCP Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name] AS FullName, [IBCCP Referral].LanguagePreferenceID, [IBCCP
Referral].Age, [IBCCP Referral].SecondaryPhone, [IBCCP
Referral].PrimaryPhone, [IBCCP Referral].Address, [IBCCP
Referral].City, [IBCCP Referral].Zip, [IBCCP Referral].[County Code
ID], [IBCCP Agencies].Name, [IBCCP Referral].OtherReferralAgency,
[IBCCP Referral].AgencyContactYes, [IBCCP Referral].AgencyContactNo,
[IBCCP Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP
Referral].QualityAssurance, [IBCCP Referral].Comments, [IBCCP
Referral].CallDate
FROM [IBCCP Agencies] INNER JOIN [IBCCP Referral] ON [IBCCP
Agencies].ID = [IBCCP Referral].AgencyID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallTaker,
[IBCCP Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name], [IBCCP Referral].LanguagePreferenceID, [IBCCP Referral].Age,
[IBCCP Referral].SecondaryPhone, [IBCCP Referral].PrimaryPhone, [IBCCP
Referral].Address, [IBCCP Referral].City, [IBCCP Referral].Zip, [IBCCP
Referral].[County Code ID], [IBCCP Agencies].Name, [IBCCP
Referral].OtherReferralAgency, [IBCCP Referral].AgencyContactYes,
[IBCCP Referral].AgencyContactNo, [IBCCP
Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP
Referral].QualityAssurance, [IBCCP Referral].Comments, [IBCCP
Referral].CallDate, DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID]) Mod 10
HAVING ((([IBCCP Referral].CallDate) Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStart] And [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStop]) AND ((DCount("[Caller
ID]","IBCCP Referral","[Caller ID] <= " & [Caller ID]) Mod 10)=0))
ORDER BY [IBCCP Referral].[Caller ID];

The second report is a QA report that returns every 5th report for the
date range entered. The code of the query behind the report follows:

SELECT [IBCCP Referral].[Caller ID], [IBCCP Referral].CallTaker,
[IBCCP Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name] AS FullName, [IBCCP Referral].LanguagePreferenceID, [IBCCP
Referral].Age, [IBCCP Referral].SecondaryPhone, [IBCCP
Referral].PrimaryPhone, [IBCCP Referral].Address, [IBCCP
Referral].City, [IBCCP Referral].Zip, [IBCCP Referral].[County Code
ID], [IBCCP Agencies].Name, [IBCCP Referral].OtherReferralAgency,
[IBCCP Referral].AgencyContactYes, [IBCCP Referral].AgencyContactNo,
[IBCCP Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP
Referral].QualityAssurance, [IBCCP Referral].Comments, [IBCCP
Referral].CallDate
FROM [IBCCP Agencies] INNER JOIN [IBCCP Referral] ON [IBCCP
Agencies].ID = [IBCCP Referral].AgencyID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallTaker,
[IBCCP Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name], [IBCCP Referral].LanguagePreferenceID, [IBCCP Referral].Age,
[IBCCP Referral].SecondaryPhone, [IBCCP Referral].PrimaryPhone, [IBCCP
Referral].Address, [IBCCP Referral].City, [IBCCP Referral].Zip, [IBCCP
Referral].[County Code ID], [IBCCP Agencies].Name, [IBCCP
Referral].OtherReferralAgency, [IBCCP Referral].AgencyContactYes,
[IBCCP Referral].AgencyContactNo, [IBCCP
Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP
Referral].QualityAssurance, [IBCCP Referral].Comments, [IBCCP
Referral].CallDate, DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID]) Mod 5
HAVING ((([IBCCP Referral].CallDate) Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStartEvery5thRprt] And [Forms]!
[Main]![frmIBCCPLabelsReports].[Form].[txtQAStopEvery5thRprt]) AND
((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller
ID]) Mod 5)=0))
ORDER BY [IBCCP Referral].[Caller ID];

The third report is a QA Summary report that returns every 10th report
for the date range entered. This report also contains a summary of
the QA calls made to clients:

-Percent of respondents contacted by agency (percentage Yes,
percentage No).
-Percent of respondents contacted by agency (percentage after 1 week,
percentage after 2weeks, percentage after 3or more weeks).
-Percent of respondents that became a client (percentage Yes,
percentage No, percentage Don’t Know).
-Percentage of respondents satisfied with the help they received
(percentage Yes, percentage No).
-Percent of respondents in need of further assistance (percentage Yes,
percentage No).
-Total number of QA calls that were made.
-Total number of QA callers that responded.

The code of the four queries behind the report follows. This is the
code of the first query:

SELECT [IBCCP Referral].[Caller ID], Count([IBCCP Referral].[Caller
ID]) AS [CountOfCaller ID], [IBCCP Referral].CallDate
FROM [IBCCP Referral] INNER JOIN [IBCCP Agencies] ON [IBCCP
Referral].AgencyID = [IBCCP Agencies].ID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallDate,
DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller ID])
Mod 10
HAVING ((([IBCCP Referral].CallDate) Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtStartSumRptEvery10th] And [Forms]!
[Main]![frmIBCCPLabelsReports].[Form].[txtStopSumRptEvery10th]) AND
((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller
ID]) Mod 10)=0))
ORDER BY [IBCCP Referral].[Caller ID];

The second query:

SELECT Sum(qryCountQACallsforEvery10thReport.[CountOfCaller ID]) AS
[SumOfCountOfCaller ID]
FROM qryCountQACallsforEvery10thReport;

The third query:

SELECT Min([IBCCP Referral].AgencyContactYes) AS
MinOfAgencyContactYes, Min([IBCCP Referral].AgencyContactNo) AS
MinOfAgencyContactNo, Min([IBCCP
Referral].DaysBeforeHeardFromAgency1Week) AS
MinOfDaysBeforeHeardFromAgency1Week, Min([IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks) AS
MinOfDaysBeforeHeardFromAgency2Weeks, Min([IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks) AS
MinOfDaysBeforeHeardFromAgency3orMoreWeeks, Min([IBCCP
Referral].BecomeIBCCPClientYes) AS MinOfBecomeIBCCPClientYes, Min
([IBCCP Referral].BecomeIBCCPClientNo) AS MinOfBecomeIBCCPClientNo, Min
([IBCCP Referral].BecomeIBCCPClientDontKnow) AS
MinOfBecomeIBCCPClientDontKnow, Min([IBCCP
Referral].SatisfiedWithHelpReceivedYes) AS
MinOfSatisfiedWithHelpReceivedYes, Min([IBCCP
Referral].SatisfiedWithHelpReceivedNo) AS
MinOfSatisfiedWithHelpReceivedNo, Min([IBCCP
Referral].InNeedFurtherAssistanceYes) AS
MinOfInNeedFurtherAssistanceYes, Min([IBCCP
Referral].InNeedFurtherAssistanceNo) AS
MinOfInNeedFurtherAssistanceNo, qryTLNmbrQACallsMadeEvery10thReport.
[SumOfCountOfCaller ID], Min([IBCCP Referral].QualityAssurance) AS
MinOfQualityAssurance
FROM [IBCCP Referral], qryTLNmbrQACallsMadeEvery10thReport
GROUP BY qryTLNmbrQACallsMadeEvery10thReport.[SumOfCountOfCaller ID];

The fourth query:

SELECT Sum(Abs([MinOfAgencyContactYes])) AS AgencyContactYes, Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactNo, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week])) AS
DaysBeforeHeardFromAgency1Week, Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks])) AS
DaysBeforeHeardFromAgency2Weeks, Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgency3orMoreWeeks, Sum(Abs
([MinOfBecomeIBCCPClientYes])) AS BecomeIBCCPClientYes, Sum(Abs
([MinOfBecomeIBCCPClientNo])) AS BecomeIBCCPClientNo, Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientDontKnow, Sum
(Abs([MinOfSatisfiedWithHelpReceivedYes])) AS
SatisfiedWithHelpReceivedYes, Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedNo,
Sum(Abs([MinOfInNeedFurtherAssistanceYes])) AS
InNeedFurtherAssistanceYes, Sum(Abs([MinOfInNeedFurtherAssistanceNo]))
AS InNeedFurtherAssistanceNo, Sum(Abs([MinOfAgencyContactYes]))+Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactTL, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgencyTL, Sum(Abs([MinOfBecomeIBCCPClientYes]))+Sum
(Abs([MinOfBecomeIBCCPClientNo]))+Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientTL, Sum(Abs
([MinOfSatisfiedWithHelpReceivedYes]))+Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedTL,
Sum(Abs([MinOfInNeedFurtherAssistanceYes]))+Sum(Abs
([MinOfInNeedFurtherAssistanceNo])) AS InNeedFurtherAssistanceTL,
([AgencyContactYes])/([AgencyContactTL]) AS PrcntAgencyContactYes,
[AgencyContactNo]/[AgencyContactTL] AS PrcntAgencyContactNo,
[DaysBeforeHeardFromAgency1Week]/[DaysBeforeHeardFromAgencyTL] AS
PrcntDaysBeforeHeardFromAgency1Week, [DaysBeforeHeardFromAgency2Weeks]/
[DaysBeforeHeardFromAgencyTL] AS PrcntDaysBeforeHeardFromAgency2Weeks,
[DaysBeforeHeardFromAgency3orMoreWeeks]/[DaysBeforeHeardFromAgencyTL]
AS PrcntDaysBeforeHeardFromAgency3orMoreWeeks, [BecomeIBCCPClientYes]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientYes,
[BecomeIBCCPClientNo]/[BecomeIBCCPClientTL] AS
PrcntBecomeIBCCPClientNo, [BecomeIBCCPClientDontKnow]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientDontKnow,
[SatisfiedWithHelpReceivedYes]/[SatisfiedWithHelpReceivedTL] AS
PrcntSatisfiedWithHelpReceivedYes, [SatisfiedWithHelpReceivedNo]/
[SatisfiedWithHelpReceivedTL] AS PrcntSatisfiedWithHelpReceivedNo,
[InNeedFurtherAssistanceYes]/[InNeedFurtherAssistanceTL] AS
PrcntInNeedFurtherAssistanceYes, [InNeedFurtherAssistanceNo]/
[InNeedFurtherAssistanceTL] AS PrcntInNeedFurtherAssistanceNo, (Sum(Abs
([SumOfCountOfCaller ID]))) AS TLQACallsMade10th, Sum(Abs
([MinOfQualityAssurance])) AS TLQACallersResponded10th
FROM qrySummaryReportResponsesTEST
WITH OWNERACCESS OPTION;

The fourth report is a QA Summary report that returns every 5th report
for the date range entered. This report also contains a summary of
the QA calls made to clients:

-Percent of respondents contacted by agency (percentage Yes,
percentage No).
-Percent of respondents contacted by agency (percentage after 1 week,
percentage after 2weeks, percentage after 3or more weeks).
-Percent of respondents that became a client (percentage Yes,
percentage No, percentage Don’t Know).
-Percentage of respondents satisfied with the help they received
(percentage Yes, percentage No).
-Percent of respondents in need of further assistance (percentage Yes,
percentage No).
-Total number of QA calls that were made.
-Total number of QA callers that responded.

The code of the four queries behind the report follows. This is the
code of the first query:

SELECT [IBCCP Referral].[Caller ID], Count([IBCCP Referral].[Caller
ID]) AS [CountOfCaller ID], [IBCCP Referral].CallDate
FROM [IBCCP Referral] INNER JOIN [IBCCP Agencies] ON [IBCCP
Referral].AgencyID = [IBCCP Agencies].ID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallDate,
DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller ID])
Mod 5
HAVING ((([IBCCP Referral].CallDate) Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStartSum5thRprt] And [Forms]!
[Main]![frmIBCCPLabelsReports].[Form].[txtQAStopSum5thRprt]) AND
((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller
ID]) Mod 5)=0))
ORDER BY [IBCCP Referral].[Caller ID];

The second query:

SELECT Sum(qryCountQACallsforEvery5thReport.[CountOfCaller ID]) AS
[SumOfCountOfCaller ID]
FROM qryCountQACallsforEvery5thReport;

The third query:

SELECT Min([IBCCP Referral].AgencyContactYes) AS
MinOfAgencyContactYes, Min([IBCCP Referral].AgencyContactNo) AS
MinOfAgencyContactNo, Min([IBCCP
Referral].DaysBeforeHeardFromAgency1Week) AS
MinOfDaysBeforeHeardFromAgency1Week, Min([IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks) AS
MinOfDaysBeforeHeardFromAgency2Weeks, Min([IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks) AS
MinOfDaysBeforeHeardFromAgency3orMoreWeeks, Min([IBCCP
Referral].BecomeIBCCPClientYes) AS MinOfBecomeIBCCPClientYes, Min
([IBCCP Referral].BecomeIBCCPClientNo) AS MinOfBecomeIBCCPClientNo, Min
([IBCCP Referral].BecomeIBCCPClientDontKnow) AS
MinOfBecomeIBCCPClientDontKnow, Min([IBCCP
Referral].SatisfiedWithHelpReceivedYes) AS
MinOfSatisfiedWithHelpReceivedYes, Min([IBCCP
Referral].SatisfiedWithHelpReceivedNo) AS
MinOfSatisfiedWithHelpReceivedNo, Min([IBCCP
Referral].InNeedFurtherAssistanceYes) AS
MinOfInNeedFurtherAssistanceYes, Min([IBCCP
Referral].InNeedFurtherAssistanceNo) AS
MinOfInNeedFurtherAssistanceNo, Min([IBCCP Referral].QualityAssurance)
AS MinOfQualityAssurance, qryTLNmbrQACallsMadeEvery5thReport.
[SumOfCountOfCaller ID]
FROM [IBCCP Referral], qryTLNmbrQACallsMadeEvery5thReport
GROUP BY qryTLNmbrQACallsMadeEvery5thReport.[SumOfCountOfCaller ID];

The fourth query:

SELECT Sum(Abs([MinOfAgencyContactYes])) AS AgencyContactYes, Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactNo, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week])) AS
DaysBeforeHeardFromAgency1Week, Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks])) AS
DaysBeforeHeardFromAgency2Weeks, Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgency3orMoreWeeks, Sum(Abs
([MinOfBecomeIBCCPClientYes])) AS BecomeIBCCPClientYes, Sum(Abs
([MinOfBecomeIBCCPClientNo])) AS BecomeIBCCPClientNo, Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientDontKnow, Sum
(Abs([MinOfSatisfiedWithHelpReceivedYes])) AS
SatisfiedWithHelpReceivedYes, Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedNo,
Sum(Abs([MinOfInNeedFurtherAssistanceYes])) AS
InNeedFurtherAssistanceYes, Sum(Abs([MinOfInNeedFurtherAssistanceNo]))
AS InNeedFurtherAssistanceNo, Sum(Abs([MinOfAgencyContactYes]))+Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactTL, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgencyTL, Sum(Abs([MinOfBecomeIBCCPClientYes]))+Sum
(Abs([MinOfBecomeIBCCPClientNo]))+Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientTL, Sum(Abs
([MinOfSatisfiedWithHelpReceivedYes]))+Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedTL,
Sum(Abs([MinOfInNeedFurtherAssistanceYes]))+Sum(Abs
([MinOfInNeedFurtherAssistanceNo])) AS InNeedFurtherAssistanceTL,
([AgencyContactYes])/([AgencyContactTL]) AS PrcntAgencyContactYes,
[AgencyContactNo]/[AgencyContactTL] AS PrcntAgencyContactNo,
[DaysBeforeHeardFromAgency1Week]/[DaysBeforeHeardFromAgencyTL] AS
PrcntDaysBeforeHeardFromAgency1Week, [DaysBeforeHeardFromAgency2Weeks]/
[DaysBeforeHeardFromAgencyTL] AS PrcntDaysBeforeHeardFromAgency2Weeks,
[DaysBeforeHeardFromAgency3orMoreWeeks]/[DaysBeforeHeardFromAgencyTL]
AS PrcntDaysBeforeHeardFromAgency3orMoreWeeks, [BecomeIBCCPClientYes]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientYes,
[BecomeIBCCPClientNo]/[BecomeIBCCPClientTL] AS
PrcntBecomeIBCCPClientNo, [BecomeIBCCPClientDontKnow]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientDontKnow,
[SatisfiedWithHelpReceivedYes]/[SatisfiedWithHelpReceivedTL] AS
PrcntSatisfiedWithHelpReceivedYes, [SatisfiedWithHelpReceivedNo]/
[SatisfiedWithHelpReceivedTL] AS PrcntSatisfiedWithHelpReceivedNo,
[InNeedFurtherAssistanceYes]/[InNeedFurtherAssistanceTL] AS
PrcntInNeedFurtherAssistanceYes, [InNeedFurtherAssistanceNo]/
[InNeedFurtherAssistanceTL] AS PrcntInNeedFurtherAssistanceNo, (Sum(Abs
([SumOfCountOfCaller ID]))) AS TLQACallsMade10th, Sum(Abs
([MinOfQualityAssurance])) AS TLQACallersResponded10th
FROM qrySummaryReportResponsesTestQA5thRpt
WITH OWNERACCESS OPTION;
 
B

Bob Barrows

zufie said:
Some of my Reports return results after 15 mins!

What can I do to make thses reports return results faster?

Include an Index?

Any ideas?

Thanks!

John
Click to expand...

Back up your database. Go to Tools>Analyze>Performance and run the
wizard on your queries. Allow it to implement the suggestions it makes
and see if they help. If not, revert to your backup.
 
J

John Spencer

One thing you can do is remove the group by clause since it is just
slowing things down for no practical reason. Also make sure you have
indexes on [Caller ID] and CallDate

SELECT DISTINCT [IBCCP Referral].[Caller ID]
, [IBCCP Referral].CallTaker
, [IBCCP Referral].ProviderNameNumber
, [IBCCP Referral].HowHear
, [IBCCP Referral].[IRIS Referral Number]
, [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name] AS FullName
, [IBCCP Referral].LanguagePreferenceID
, [IBCCP Referral].Age
, [IBCCP Referral].SecondaryPhone
, [IBCCP Referral].PrimaryPhone
, [IBCCP Referral].Address
, [IBCCP Referral].City
, [IBCCP Referral].Zip
, [IBCCP Referral].[County Code ID]
, [IBCCP Agencies].Name
, [IBCCP Referral].OtherReferralAgency
, [IBCCP Referral].AgencyContactYes
, [IBCCP Referral].AgencyContactNo
, [IBCCP Referral].DaysBeforeHeardFromAgency1Week
, [IBCCP Referral].DaysBeforeHeardFromAgency2Weeks
, [IBCCP Referral].DaysBeforeHeardFromAgency3orMoreWeeks
, [IBCCP Referral].BecomeIBCCPClientYes
, [IBCCP Referral].BecomeIBCCPClientNo
, [IBCCP Referral].BecomeIBCCPClientDontKnow
, [IBCCP Referral].SatisfiedWithHelpReceivedYes
, [IBCCP Referral].SatisfiedWithHelpReceivedNo
, [IBCCP Referral].InNeedFurtherAssistanceYes
, [IBCCP Referral].InNeedFurtherAssistanceNo
, [IBCCP Referral].QualityAssurance
, [IBCCP Referral].Comments
, [IBCCP Referral].CallDate
FROM [IBCCP Agencies] INNER JOIN [IBCCP Referral]
ON [IBCCP Agencies].ID = [IBCCP Referral].AgencyID
WHERE IBCCP Referral].CallDate Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStart] And [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStop]

AND DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " &
[Caller ID]) Mod 10=0

ORDER BY [IBCCP Referral].[Caller ID];

I might try using this subquery in the where clause
SELECT A.[Caller ID]
FROM [IBCCP Referral] as A LEFT JOIN [IBCCP Referral] as B
ON A.[Caller ID] < B.[Caller ID]
GROUP BY A.[Caller ID]
HAVING Count(B.[Caller ID]) Mod 10 = 0

So the where clause would look like the following

WHERE [IBCCP Referral].CallDate Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStart] And [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStop]
AND [IBCCP Referral].[Caller Id] IN
(SELECT A.[Caller ID]
FROM [IBCCP Referral] as A LEFT JOIN [IBCCP Referral] as B
ON A.[Caller ID] < B.[Caller ID]
GROUP BY A.[Caller ID]
HAVING Count(B.[Caller ID]) Mod 10 = 0)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Some of myReportsreturnresultsafter15mins!

What can I do to make thsesreportsreturnresultsfaster?

Include an Index?

Any ideas?

Thanks!

John
Click to expand...

Sorry.
The first report is a QA report that returns every 10th report for the
date range entered. The code of the query behind the report follows:

SELECT [IBCCP Referral].[Caller ID], [IBCCP Referral].CallTaker,
[IBCCP Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name] AS FullName, [IBCCP Referral].LanguagePreferenceID, [IBCCP
Referral].Age, [IBCCP Referral].SecondaryPhone, [IBCCP
Referral].PrimaryPhone, [IBCCP Referral].Address, [IBCCP
Referral].City, [IBCCP Referral].Zip, [IBCCP Referral].[County Code
ID], [IBCCP Agencies].Name, [IBCCP Referral].OtherReferralAgency,
[IBCCP Referral].AgencyContactYes, [IBCCP Referral].AgencyContactNo,
[IBCCP Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP
Referral].QualityAssurance, [IBCCP Referral].Comments, [IBCCP
Referral].CallDate
FROM [IBCCP Agencies] INNER JOIN [IBCCP Referral] ON [IBCCP
Agencies].ID = [IBCCP Referral].AgencyID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallTaker,
[IBCCP Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name], [IBCCP Referral].LanguagePreferenceID, [IBCCP Referral].Age,
[IBCCP Referral].SecondaryPhone, [IBCCP Referral].PrimaryPhone, [IBCCP
Referral].Address, [IBCCP Referral].City, [IBCCP Referral].Zip, [IBCCP
Referral].[County Code ID], [IBCCP Agencies].Name, [IBCCP
Referral].OtherReferralAgency, [IBCCP Referral].AgencyContactYes,
[IBCCP Referral].AgencyContactNo, [IBCCP
Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP
Referral].QualityAssurance, [IBCCP Referral].Comments, [IBCCP
Referral].CallDate, DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID]) Mod 10
HAVING ((([IBCCP Referral].CallDate) Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStart] And [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStop]) AND ((DCount("[Caller
ID]","IBCCP Referral","[Caller ID] <= " & [Caller ID]) Mod 10)=0))
ORDER BY [IBCCP Referral].[Caller ID];

The second report is a QA report that returns every 5th report for the
date range entered. The code of the query behind the report follows:

SELECT [IBCCP Referral].[Caller ID], [IBCCP Referral].CallTaker,
[IBCCP Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name] AS FullName, [IBCCP Referral].LanguagePreferenceID, [IBCCP
Referral].Age, [IBCCP Referral].SecondaryPhone, [IBCCP
Referral].PrimaryPhone, [IBCCP Referral].Address, [IBCCP
Referral].City, [IBCCP Referral].Zip, [IBCCP Referral].[County Code
ID], [IBCCP Agencies].Name, [IBCCP Referral].OtherReferralAgency,
[IBCCP Referral].AgencyContactYes, [IBCCP Referral].AgencyContactNo,
[IBCCP Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP
Referral].QualityAssurance, [IBCCP Referral].Comments, [IBCCP
Referral].CallDate
FROM [IBCCP Agencies] INNER JOIN [IBCCP Referral] ON [IBCCP
Agencies].ID = [IBCCP Referral].AgencyID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallTaker,
[IBCCP Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name], [IBCCP Referral].LanguagePreferenceID, [IBCCP Referral].Age,
[IBCCP Referral].SecondaryPhone, [IBCCP Referral].PrimaryPhone, [IBCCP
Referral].Address, [IBCCP Referral].City, [IBCCP Referral].Zip, [IBCCP
Referral].[County Code ID], [IBCCP Agencies].Name, [IBCCP
Referral].OtherReferralAgency, [IBCCP Referral].AgencyContactYes,
[IBCCP Referral].AgencyContactNo, [IBCCP
Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP
Referral].QualityAssurance, [IBCCP Referral].Comments, [IBCCP
Referral].CallDate, DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID]) Mod 5
HAVING ((([IBCCP Referral].CallDate) Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStartEvery5thRprt] And [Forms]!
[Main]![frmIBCCPLabelsReports].[Form].[txtQAStopEvery5thRprt]) AND
((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller
ID]) Mod 5)=0))
ORDER BY [IBCCP Referral].[Caller ID];

The third report is a QA Summary report that returns every 10th report
for the date range entered. This report also contains a summary of
the QA calls made to clients:

-Percent of respondents contacted by agency (percentage Yes,
percentage No).
-Percent of respondents contacted by agency (percentage after 1 week,
percentage after 2weeks, percentage after 3or more weeks).
-Percent of respondents that became a client (percentage Yes,
percentage No, percentage Don’t Know).
-Percentage of respondents satisfied with the help they received
(percentage Yes, percentage No).
-Percent of respondents in need of further assistance (percentage Yes,
percentage No).
-Total number of QA calls that were made.
-Total number of QA callers that responded.

The code of the four queries behind the report follows. This is the
code of the first query:

SELECT [IBCCP Referral].[Caller ID], Count([IBCCP Referral].[Caller
ID]) AS [CountOfCaller ID], [IBCCP Referral].CallDate
FROM [IBCCP Referral] INNER JOIN [IBCCP Agencies] ON [IBCCP
Referral].AgencyID = [IBCCP Agencies].ID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallDate,
DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller ID])
Mod 10
HAVING ((([IBCCP Referral].CallDate) Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtStartSumRptEvery10th] And [Forms]!
[Main]![frmIBCCPLabelsReports].[Form].[txtStopSumRptEvery10th]) AND
((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller
ID]) Mod 10)=0))
ORDER BY [IBCCP Referral].[Caller ID];

The second query:

SELECT Sum(qryCountQACallsforEvery10thReport.[CountOfCaller ID]) AS
[SumOfCountOfCaller ID]
FROM qryCountQACallsforEvery10thReport;

The third query:

SELECT Min([IBCCP Referral].AgencyContactYes) AS
MinOfAgencyContactYes, Min([IBCCP Referral].AgencyContactNo) AS
MinOfAgencyContactNo, Min([IBCCP
Referral].DaysBeforeHeardFromAgency1Week) AS
MinOfDaysBeforeHeardFromAgency1Week, Min([IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks) AS
MinOfDaysBeforeHeardFromAgency2Weeks, Min([IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks) AS
MinOfDaysBeforeHeardFromAgency3orMoreWeeks, Min([IBCCP
Referral].BecomeIBCCPClientYes) AS MinOfBecomeIBCCPClientYes, Min
([IBCCP Referral].BecomeIBCCPClientNo) AS MinOfBecomeIBCCPClientNo, Min
([IBCCP Referral].BecomeIBCCPClientDontKnow) AS
MinOfBecomeIBCCPClientDontKnow, Min([IBCCP
Referral].SatisfiedWithHelpReceivedYes) AS
MinOfSatisfiedWithHelpReceivedYes, Min([IBCCP
Referral].SatisfiedWithHelpReceivedNo) AS
MinOfSatisfiedWithHelpReceivedNo, Min([IBCCP
Referral].InNeedFurtherAssistanceYes) AS
MinOfInNeedFurtherAssistanceYes, Min([IBCCP
Referral].InNeedFurtherAssistanceNo) AS
MinOfInNeedFurtherAssistanceNo, qryTLNmbrQACallsMadeEvery10thReport.
[SumOfCountOfCaller ID], Min([IBCCP Referral].QualityAssurance) AS
MinOfQualityAssurance
FROM [IBCCP Referral], qryTLNmbrQACallsMadeEvery10thReport
GROUP BY qryTLNmbrQACallsMadeEvery10thReport.[SumOfCountOfCaller ID];

The fourth query:

SELECT Sum(Abs([MinOfAgencyContactYes])) AS AgencyContactYes, Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactNo, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week])) AS
DaysBeforeHeardFromAgency1Week, Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks])) AS
DaysBeforeHeardFromAgency2Weeks, Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgency3orMoreWeeks, Sum(Abs
([MinOfBecomeIBCCPClientYes])) AS BecomeIBCCPClientYes, Sum(Abs
([MinOfBecomeIBCCPClientNo])) AS BecomeIBCCPClientNo, Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientDontKnow, Sum
(Abs([MinOfSatisfiedWithHelpReceivedYes])) AS
SatisfiedWithHelpReceivedYes, Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedNo,
Sum(Abs([MinOfInNeedFurtherAssistanceYes])) AS
InNeedFurtherAssistanceYes, Sum(Abs([MinOfInNeedFurtherAssistanceNo]))
AS InNeedFurtherAssistanceNo, Sum(Abs([MinOfAgencyContactYes]))+Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactTL, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgencyTL, Sum(Abs([MinOfBecomeIBCCPClientYes]))+Sum
(Abs([MinOfBecomeIBCCPClientNo]))+Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientTL, Sum(Abs
([MinOfSatisfiedWithHelpReceivedYes]))+Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedTL,
Sum(Abs([MinOfInNeedFurtherAssistanceYes]))+Sum(Abs
([MinOfInNeedFurtherAssistanceNo])) AS InNeedFurtherAssistanceTL,
([AgencyContactYes])/([AgencyContactTL]) AS PrcntAgencyContactYes,
[AgencyContactNo]/[AgencyContactTL] AS PrcntAgencyContactNo,
[DaysBeforeHeardFromAgency1Week]/[DaysBeforeHeardFromAgencyTL] AS
PrcntDaysBeforeHeardFromAgency1Week, [DaysBeforeHeardFromAgency2Weeks]/
[DaysBeforeHeardFromAgencyTL] AS PrcntDaysBeforeHeardFromAgency2Weeks,
[DaysBeforeHeardFromAgency3orMoreWeeks]/[DaysBeforeHeardFromAgencyTL]
AS PrcntDaysBeforeHeardFromAgency3orMoreWeeks, [BecomeIBCCPClientYes]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientYes,
[BecomeIBCCPClientNo]/[BecomeIBCCPClientTL] AS
PrcntBecomeIBCCPClientNo, [BecomeIBCCPClientDontKnow]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientDontKnow,
[SatisfiedWithHelpReceivedYes]/[SatisfiedWithHelpReceivedTL] AS
PrcntSatisfiedWithHelpReceivedYes, [SatisfiedWithHelpReceivedNo]/
[SatisfiedWithHelpReceivedTL] AS PrcntSatisfiedWithHelpReceivedNo,
[InNeedFurtherAssistanceYes]/[InNeedFurtherAssistanceTL] AS
PrcntInNeedFurtherAssistanceYes, [InNeedFurtherAssistanceNo]/
[InNeedFurtherAssistanceTL] AS PrcntInNeedFurtherAssistanceNo, (Sum(Abs
([SumOfCountOfCaller ID]))) AS TLQACallsMade10th, Sum(Abs
([MinOfQualityAssurance])) AS TLQACallersResponded10th
FROM qrySummaryReportResponsesTEST
WITH OWNERACCESS OPTION;

The fourth report is a QA Summary report that returns every 5th report
for the date range entered. This report also contains a summary of
the QA calls made to clients:

-Percent of respondents contacted by agency (percentage Yes,
percentage No).
-Percent of respondents contacted by agency (percentage after 1 week,
percentage after 2weeks, percentage after 3or more weeks).
-Percent of respondents that became a client (percentage Yes,
percentage No, percentage Don’t Know).
-Percentage of respondents satisfied with the help they received
(percentage Yes, percentage No).
-Percent of respondents in need of further assistance (percentage Yes,
percentage No).
-Total number of QA calls that were made.
-Total number of QA callers that responded.

The code of the four queries behind the report follows. This is the
code of the first query:

SELECT [IBCCP Referral].[Caller ID], Count([IBCCP Referral].[Caller
ID]) AS [CountOfCaller ID], [IBCCP Referral].CallDate
FROM [IBCCP Referral] INNER JOIN [IBCCP Agencies] ON [IBCCP
Referral].AgencyID = [IBCCP Agencies].ID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallDate,
DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller ID])
Mod 5
HAVING ((([IBCCP Referral].CallDate) Between [Forms]![Main]!
[frmIBCCPLabelsReports].[Form].[txtQAStartSum5thRprt] And [Forms]!
[Main]![frmIBCCPLabelsReports].[Form].[txtQAStopSum5thRprt]) AND
((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " & [Caller
ID]) Mod 5)=0))
ORDER BY [IBCCP Referral].[Caller ID];

The second query:

SELECT Sum(qryCountQACallsforEvery5thReport.[CountOfCaller ID]) AS
[SumOfCountOfCaller ID]
FROM qryCountQACallsforEvery5thReport;

The third query:

SELECT Min([IBCCP Referral].AgencyContactYes) AS
MinOfAgencyContactYes, Min([IBCCP Referral].AgencyContactNo) AS
MinOfAgencyContactNo, Min([IBCCP
Referral].DaysBeforeHeardFromAgency1Week) AS
MinOfDaysBeforeHeardFromAgency1Week, Min([IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks) AS
MinOfDaysBeforeHeardFromAgency2Weeks, Min([IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks) AS
MinOfDaysBeforeHeardFromAgency3orMoreWeeks, Min([IBCCP
Referral].BecomeIBCCPClientYes) AS MinOfBecomeIBCCPClientYes, Min
([IBCCP Referral].BecomeIBCCPClientNo) AS MinOfBecomeIBCCPClientNo, Min
([IBCCP Referral].BecomeIBCCPClientDontKnow) AS
MinOfBecomeIBCCPClientDontKnow, Min([IBCCP
Referral].SatisfiedWithHelpReceivedYes) AS
MinOfSatisfiedWithHelpReceivedYes, Min([IBCCP
Referral].SatisfiedWithHelpReceivedNo) AS
MinOfSatisfiedWithHelpReceivedNo, Min([IBCCP
Referral].InNeedFurtherAssistanceYes) AS
MinOfInNeedFurtherAssistanceYes, Min([IBCCP
Referral].InNeedFurtherAssistanceNo) AS
MinOfInNeedFurtherAssistanceNo, Min([IBCCP Referral].QualityAssurance)
AS MinOfQualityAssurance, qryTLNmbrQACallsMadeEvery5thReport.
[SumOfCountOfCaller ID]
FROM [IBCCP Referral], qryTLNmbrQACallsMadeEvery5thReport
GROUP BY qryTLNmbrQACallsMadeEvery5thReport.[SumOfCountOfCaller ID];

The fourth query:

SELECT Sum(Abs([MinOfAgencyContactYes])) AS AgencyContactYes, Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactNo, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week])) AS
DaysBeforeHeardFromAgency1Week, Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks])) AS
DaysBeforeHeardFromAgency2Weeks, Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgency3orMoreWeeks, Sum(Abs
([MinOfBecomeIBCCPClientYes])) AS BecomeIBCCPClientYes, Sum(Abs
([MinOfBecomeIBCCPClientNo])) AS BecomeIBCCPClientNo, Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientDontKnow, Sum
(Abs([MinOfSatisfiedWithHelpReceivedYes])) AS
SatisfiedWithHelpReceivedYes, Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedNo,
Sum(Abs([MinOfInNeedFurtherAssistanceYes])) AS
InNeedFurtherAssistanceYes, Sum(Abs([MinOfInNeedFurtherAssistanceNo]))
AS InNeedFurtherAssistanceNo, Sum(Abs([MinOfAgencyContactYes]))+Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactTL, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgencyTL, Sum(Abs([MinOfBecomeIBCCPClientYes]))+Sum
(Abs([MinOfBecomeIBCCPClientNo]))+Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientTL, Sum(Abs
([MinOfSatisfiedWithHelpReceivedYes]))+Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedTL,
Sum(Abs([MinOfInNeedFurtherAssistanceYes]))+Sum(Abs
([MinOfInNeedFurtherAssistanceNo])) AS InNeedFurtherAssistanceTL,
([AgencyContactYes])/([AgencyContactTL]) AS PrcntAgencyContactYes,
[AgencyContactNo]/[AgencyContactTL] AS PrcntAgencyContactNo,
[DaysBeforeHeardFromAgency1Week]/[DaysBeforeHeardFromAgencyTL] AS
PrcntDaysBeforeHeardFromAgency1Week, [DaysBeforeHeardFromAgency2Weeks]/
[DaysBeforeHeardFromAgencyTL] AS PrcntDaysBeforeHeardFromAgency2Weeks,
[DaysBeforeHeardFromAgency3orMoreWeeks]/[DaysBeforeHeardFromAgencyTL]
AS PrcntDaysBeforeHeardFromAgency3orMoreWeeks, [BecomeIBCCPClientYes]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientYes,
[BecomeIBCCPClientNo]/[BecomeIBCCPClientTL] AS
PrcntBecomeIBCCPClientNo, [BecomeIBCCPClientDontKnow]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientDontKnow,
[SatisfiedWithHelpReceivedYes]/[SatisfiedWithHelpReceivedTL] AS
PrcntSatisfiedWithHelpReceivedYes, [SatisfiedWithHelpReceivedNo]/
[SatisfiedWithHelpReceivedTL] AS PrcntSatisfiedWithHelpReceivedNo,
[InNeedFurtherAssistanceYes]/[InNeedFurtherAssistanceTL] AS
PrcntInNeedFurtherAssistanceYes, [InNeedFurtherAssistanceNo]/
[InNeedFurtherAssistanceTL] AS PrcntInNeedFurtherAssistanceNo, (Sum(Abs
([SumOfCountOfCaller ID]))) AS TLQACallsMade10th, Sum(Abs
([MinOfQualityAssurance])) AS TLQACallersResponded10th
FROM qrySummaryReportResponsesTestQA5thRpt
WITH OWNERACCESS OPTION;
Click to expand...
 

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

Return multiple fields when doing MAX calculation 2
Access Access Reporting 1
Problem with 'IIF' funtion in query 2
Time Calculations 5
Need help calculating queries & reports 8
Access 2007 SQL Stmt returns the incorrect value ? 3
reformatting text?? 2
Incorrect query result???? 2

Top