Order of execution in a report

  • Thread starter Thread starter LAS
  • Start date Start date
L

LAS

I would like to put the result of a fairly significant SQL statement in the
group footer of my report. The data is not dependent on the details of the
report. Will this SQL get silently executed for every row in the detail
section anyway, as if it might be collecting a total?

TIA
LAS
 
It depends.

You might be better off creating a sub-report to show the results in the group
footer.

Are you trying to show only one value from the SQL statement in the group
footer or multiple values?

Is the SQL statement a sub-query in the query you are using for the main report?

Does the data returned change based on which "group" is the involved?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
John Spencer said:
It depends.

You might be better off creating a sub-report to show the results in the
group footer.

Are you trying to show only one value from the SQL statement in the group
footer or multiple values?
Click to expand...

I'm building a string by cycling through multiple rows returned by the SQL
statement. The function executing the SQL statement returns only one value,
the string.
Is the SQL statement a sub-query in the query you are using for the main
report?
Click to expand...

I'm not sure what you mean here. It's using different tables.
 
I would like to put the result of a fairly significant SQL
statement in the group footer of my report. The data is not
dependent on the details of the report. Will this SQL get
silently executed for every row in the detail section anyway, as
if it might be collecting a total?
Click to expand...

Like John, the first thing that occurs to me is a subreport.

However, you could also write a function that returns the value and
use that in a control with the function as the ControlSource.
 
Actually, I should have been clearer. My plan was to write a function. Do
I assume correctly from your response that a function won't be executed over
and over for every detail line that is processed?
 
My plan was to write a function. Do
I assume correctly from your response that a function won't be
executed over and over for every detail line that is processed?
Click to expand...

The Access expression service is pretty smart in evaluating
expressions only if they can change. For instance, in a query, if
you have a function that returns a result in each row, but does not
take any row-specific parameters, the optimizer will evaluate it
only once (since it's clear that it won't change for each row).
Likewise, when the Access expression service evaluates the
expression in your group footer, if the function doesn't take
arguments that vary with each group, it will be evaluated only once
for the whole report.
 
Back
Top