Slow query

[Leslie Isaacs]

Hello All

I have the following group of 4 nested queries, the 2nd of which (and
therefore the 4th, on which a number of reports are based) runs very
slowly - around 60-80 seconds. The largest table [x confirmed] contains
around 20,000 records.
This set of queries has taken me (with some help from this NG - thanks David
S) days to get 'right', and they do now give the required output: but too
slowly!

If anyone can suggest how I might optimise the searches, or otherwise speed
them up, I would be very grateful indeed.

The 4 queries are:

FeesDue
SELECT practices.[prac name], [x confirmed].[month number], IIf([month
number]<65,[2nd yr fee],[practices]![fee]) AS [fees due]
FROM [x confirmed] INNER JOIN practices ON [x confirmed].practice =
practices.[prac name]
GROUP BY practices.[prac name], [x confirmed].[month number], IIf([month
number]<65,[2nd yr fee],[practices]![fee]);

qry fee missing (the slow one)
SELECT [x confirmed].[month number], FeesDue.[fees due] AS FeeOutstanding,
FeesDue.[fees due], 0 AS FeeReceived, FeesDue.[prac name]
FROM ([x confirmed] LEFT JOIN [payedoc accounts] ON ([x confirmed].practice
= [payedoc accounts].practice) AND ([x confirmed].[month number] = [payedoc
accounts].mth)) LEFT JOIN FeesDue ON ([x confirmed].practice = FeesDue.[prac
name]) AND ([x confirmed].[month number] = FeesDue.[month number])
WHERE ((([payedoc accounts].practice) Is Null))
GROUP BY [x confirmed].[month number], FeesDue.[fees due], FeesDue.[fees
due], 0, FeesDue.[prac name];

qry fee short
SELECT IIf([credit] Is Null,0,[credit]) AS FeeReceived, IIf([credit] Is
Null,[Fees Due],[Fees Due]-[credit]) AS FeeOutstanding, FeesDue.[prac name],
FeesDue.[month number], FeesDue.[fees due]
FROM FeesDue INNER JOIN [payedoc accounts] ON (FeesDue.[prac name] =
[payedoc accounts].practice) AND (FeesDue.[month number] = [payedoc
accounts].mth)
WHERE (((IIf([credit] Is Null,[Fees Due],[Fees Due]-[credit]))>0));

qry combined
SELECT [qry fee missing].[prac name], [qry fee missing].[month number], [qry
fee missing].[fees due], [qry fee missing].[feeReceived], [qry fee
missing].[feeOutstanding]
FROM [qry fee missing]
UNION ALL SELECT [qry fee short].[prac name], [qry fee short].[month
number], [qry fee short].[fees due], [qry fee short].[feeReceived], [qry fee
short].[feeOutstanding]
FROM [qry fee short];



Many thanks
Leslie Isaacs

 

[David S via AccessMonster.com]

Hi Leslie,

Try the new and fixed query I posted in the other thread and see how that
performs...

David S...