This query seems to run very slowly

[andy49]

I'm using this query (qrybreakdownresultsfinal)

SELECT qryBreakDownResults.gradewhole1, qryBreakDownResults.Countgrade1, DSum("[Countgrade1]","qrybreakdownresults","[Gradewhole1]>=" & [Gradewhole1]) AS RT, Format([RT]/(select sum(CountGrade1) from qryBreakDownResults),"Percent") AS Pcen
FROM qryBreakDownResults;

The data comes from this query (qryBreakdownresults) which completes in a very short space of time


I'm looking to get these results



But this takes a couple of seconds to complete and I wondered if I could speed it up

If not, could I delay the display of the continuous form it comes from until the data has all been calculated.

[CJ_London]

using domain functions in queries is the slowest way to do it - and running off another query may introduce performance issues.

You can try to get to your final result by combining the two queries into one

or try using a subquery e.g.

(SELECT Sum([Countgrade1]) FROM qrybreakdownresults Q WHERE [Gradewhole1]>= qryBreakDownResults.[Gradewhole1]) AS RT

[andy49]

Nearly!!! Got this.


And sooooo much quicker but this is above my SQL. Can anyone see what needs changing?

[CJ_London]

you probably haven't copied my example exactly - you need to alias the table in the subquery so it is not confused with the one in the main query

qrybreakdownresults Q WHERE

[andy49]

Thanks Ajax

Am currently using this:

SELECT qryBreakDownResults.gradewhole1, qryBreakDownResults.Countgrade1, (SELECT Sum([Countgrade1]) FROM qrybreakdownresults Q WHERE [Gradewhole1]>= qryBreakDownResults.[Gradewhole1]) AS rt
FROM qryBreakDownResults;

But error returns qryBreakdownresults.[gradewhole1] isn't a field or an expression?

Any idea

[CJ_London]

I just took your table and field names from your DSum function

[andy49]

Thanks for all the help Ajax

Found this which did the job perfectly.

SELECT a1.gradewhole, a1.CountOfgradewhole, Sum(a2.countofgradewhole) AS Running_Total, Format([Running_Total]/(select sum(Countofgradewhole) from qryBreakDownResults),"Percent") AS Pcent
FROM qryBreakDownResults AS a1, qryBreakDownResults AS a2
WHERE (((a1.gradewhole)<=[a2].[gradewhole])) OR (((a1.gradewhole)=[a2].[gradewhole]) AND ((a1.CountOfgradewhole)=[a2].[CountOfgradewhole]))
GROUP BY a1.gradewhole, a1.CountOfgradewhole
ORDER BY a1.gradewhole, a1.CountOfgradewhole DESC;

[June7]

I tested Ajax's suggestion and it works perfectly. Here is the SQL:

SELECT qryBreakDownResults.gradewhole1, qryBreakDownResults.Countgrade1, (SELECT Sum([Countgrade1]) FROM qrybreakdownresults Q WHERE [Gradewhole1]>= qryBreakDownResults.[gradewhole1]) AS RT, Format([RT]/(select sum(Countgrade1) from qryBreakDownResults),"Percent") AS Pcen
FROM qryBreakDownResults;