Order of execution in a report

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
 
J

John Spencer

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
 
L

LAS

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?

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?

I'm not sure what you mean here. It's using different tables.
 
D

David W. Fenton

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?

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.
 
L

LAS

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?
 
D

David W. Fenton

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?

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.
 

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

Top