By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Question about Report Efficiency

P: n/a
Lets say I have to create a report based on the following tables:

WIDGETS MANUFACTURERS SHIPPERS

WidgetID ManufacturerID ShipperID
WidgetDesc ManufacturerName ShipperName
WidgetManID
WidgetShipID

In this example, the fields WidgetManID and WidgetShipID point to
records in the MANUFACTURERS and SHIPPERS table.

On the report I want to show the WidgetID, WidgetDesc,
ManufacturerName and ShipperName.

Is it more efficient to set the RecordSource of the report to the
WIDGETS table Left Joined to the MANUFACTURERS and SHIPPERS table OR
to set the RecordSource to just the WIDGETS table and then do
Dlookup's for the ManufacturerName and ShipperName controls?

Can anyone help?

BTW, I do have an application where I have several reports that I have
to create like this. I thought it would be easier asking the question
using a generic example.

Thanks in advance for any comments.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Try it both ways and see which one works better. I have had reports
combining tables with left joins that after a time became real dogs,
removing the left join from a look up made huge improvements. On other
reports the opposite was true. You may not see any difference at 1st but as
your tables fill-up you may need to optimize slow reports.

"Dennis" <de***************@fernald.gov> wrote in message
news:64**************************@posting.google.c om...
Lets say I have to create a report based on the following tables:

WIDGETS MANUFACTURERS SHIPPERS

WidgetID ManufacturerID ShipperID
WidgetDesc ManufacturerName ShipperName
WidgetManID
WidgetShipID

In this example, the fields WidgetManID and WidgetShipID point to
records in the MANUFACTURERS and SHIPPERS table.

On the report I want to show the WidgetID, WidgetDesc,
ManufacturerName and ShipperName.

Is it more efficient to set the RecordSource of the report to the
WIDGETS table Left Joined to the MANUFACTURERS and SHIPPERS table OR
to set the RecordSource to just the WIDGETS table and then do
Dlookup's for the ManufacturerName and ShipperName controls?

Can anyone help?

BTW, I do have an application where I have several reports that I have
to create like this. I thought it would be easier asking the question
using a generic example.

Thanks in advance for any comments.

Nov 12 '05 #2

P: n/a
On 31 Oct 2003 04:31:08 -0800, de***************@fernald.gov (Dennis)
wrote:

DLookups are slow. Only use them as a last resort.

If WIDGETS.WidgetManID is a required field, an inner join can (and
should) be used. Same for WidgetShipID.

-Tom.
Lets say I have to create a report based on the following tables:

WIDGETS MANUFACTURERS SHIPPERS

WidgetID ManufacturerID ShipperID
WidgetDesc ManufacturerName ShipperName
WidgetManID
WidgetShipID

In this example, the fields WidgetManID and WidgetShipID point to
records in the MANUFACTURERS and SHIPPERS table.

On the report I want to show the WidgetID, WidgetDesc,
ManufacturerName and ShipperName.

Is it more efficient to set the RecordSource of the report to the
WIDGETS table Left Joined to the MANUFACTURERS and SHIPPERS table OR
to set the RecordSource to just the WIDGETS table and then do
Dlookup's for the ManufacturerName and ShipperName controls?

Can anyone help?

BTW, I do have an application where I have several reports that I have
to create like this. I thought it would be easier asking the question
using a generic example.

Thanks in advance for any comments.


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.