Subreport or DLookup?

[Ron]

Hi All,

Was just wondering which is faster, a subreport or dlookup.

I've got a report with either 5 subreports or lots of dlookups. The
subreports generally have anywhere from 5 to 12 controls on them so if I
don't do the subreport thing, I'm doing TONS of dlookups.

Just wondering what others have found to be a faster way to do things. Is
there even a faster way I'm unaware of yet than these 2 options?

Thanks in advance for any help,
ron

[Larry Linson]

The only way to know for sure is to try both. Both Subforms and DLookups can
be surprisingly speedy or dead-dog slow, depending on factors that are
difficult to determine. (My _guess_ would be that Subforms were faster,
particularly if you are able to limit the contents using an indexed field,
but it's only a guess.)

Larry Linson
Microsoft Access MVP

"Ron" <ro*******************@earthlink.com> wrote in message
news:_g*************@newsread3.news.pas.earthlink. net...

Hi All,

Was just wondering which is faster, a subreport or dlookup.

I've got a report with either 5 subreports or lots of dlookups. The
subreports generally have anywhere from 5 to 12 controls on them so if I
don't do the subreport thing, I'm doing TONS of dlookups.

Just wondering what others have found to be a faster way to do things. Is
there even a faster way I'm unaware of yet than these 2 options?

Thanks in advance for any help,
ron

[Rick Wannall]

More important to your question is the fact that DLookup and subreports are
not substitutes for each other. If you have multiple rows of related data
to display, you're going to have to use a subreport.

Example: A customer can have many orders (order headers). An order header
can have many details (order lines).

If you want to print a customer and then a list of his orders, you must use
a subreport. There is no way with dlookup() to generate the multiple
linesyou would need to represent each of the lower level (order header)
records.

If this is similar to your task, the relative speed of dlookup and
subreports is not relevant.

[Ron]

I'm rewriting stuff and I'll import a ton of data into all
the tables and then I'll try it both ways under the clock. Plus, I'm going
to try some subqueries as well to see how that effects speed for this type
of thing.

The reason I asked the question in the first place is that I've written it
with the subreports option and it just seems "slow" looking. Thought I'd
ask the question and if people had lots of experience going one way over
another, I'd find out here and wouldn't have to work so hard. ::grin::
But, I guess I need to experiment--not a bad thing.

Thanks for your response, Larry. I appreciate your time.
ron

"Larry Linson" <bo*****@localhost.not> wrote in message
news:nZ2fg.6188$%C6.3544@trnddc08...

The only way to know for sure is to try both. Both Subforms and DLookups
can be surprisingly speedy or dead-dog slow, depending on factors that are
difficult to determine. (My _guess_ would be that Subforms were faster,
particularly if you are able to limit the contents using an indexed field,
but it's only a guess.)

Larry Linson
Microsoft Access MVP

"Ron" <ro*******************@earthlink.com> wrote in message
news:_g*************@newsread3.news.pas.earthlink. net...

Hi All,

Was just wondering which is faster, a subreport or dlookup.

I've got a report with either 5 subreports or lots of dlookups. The
subreports generally have anywhere from 5 to 12 controls on them so if I
don't do the subreport thing, I'm doing TONS of dlookups.

Just wondering what others have found to be a faster way to do things.
Is there even a faster way I'm unaware of yet than these 2 options?

Thanks in advance for any help,
ron

[Ron]

Thanks for your response, Rick. However, if I I was performing a task that
required one or the other, I wouldn't be asking this question. As I said in
the initial post, I have multiple controls coming from different tables
linked by a common field. Maybe I wasn't clear though about how only 1 row
of data from the sub-tables per report is being accessed, but anywhere from
5 to 12 controls (fields) per table is being used per report page. And,
I've got 5 subreports (hitting on 5 separate tables via queries) doing all
this. So, would it be faster to continue using this method, or would it
behoove me to rewrite stuff to do lots of dlookups instead--that's my
dilemma. Or, would even sub-queries be faster?

I'll experiment. But, again, thanks for your response Rick,
ron
"Rick Wannall" <wa*****@notadomain.de> wrote in message
news:MU******************@newssvr11.news.prodigy.c om...

More important to your question is the fact that DLookup and subreports
are
not substitutes for each other. If you have multiple rows of related data
to display, you're going to have to use a subreport.

Example: A customer can have many orders (order headers). An order
header
can have many details (order lines).

If you want to print a customer and then a list of his orders, you must
use
a subreport. There is no way with dlookup() to generate the multiple
linesyou would need to represent each of the lower level (order header)
records.

If this is similar to your task, the relative speed of dlookup and
subreports is not relevant.

[Rick Wannall]

Based on the little I know about the mechanics of the data-gathering and
report-formatting phases, I would bet that the subreport approach would have
to be faster. If you can get the data you want by using links between
tables, and if you properly index those tables (all have primary keys, and
the fields in other tables that contain those values as foreign keys are
indexed as well), then the data can be gathered as intelligently as the JET
engine is capable of getting it. This has to beat repeated calls to the
DLookup function during the data gathering phase.

If you do some detailed testing, I for one would be very interested in your
results.

[Ron]

"Rick Wannall" <wa*****@notadomain.de> wrote in message
news:K9*********************@newssvr29.news.prodig y.net...

Based on the little I know about the mechanics of the data-gathering and
report-formatting phases, I would bet that the subreport approach would
have
to be faster. If you can get the data you want by using links between
tables, and if you properly index those tables (all have primary keys, and
the fields in other tables that contain those values as foreign keys are
indexed as well), then the data can be gathered as intelligently as the
JET
engine is capable of getting it. This has to beat repeated calls to the
DLookup function during the data gathering phase.

If you do some detailed testing, I for one would be very interested in
your
results.

Thanks for the response, Rick.

Yeah, that's what I was thinking, but I was hoping Dlookup would be faster,
cause the subreport thing just "looks" slow. Perhaps this will be a point
where I'll have to redirect the user's attention.

I will try a couple different ways to go (subreports/indexing etc, dlookup,
subqueries instead of either of those) and I've got tons of data to test it
with, so should get a pretty good feel for what is faster when. I'll post
my results when I have them.

Thanks again,
ron