Question about Report Efficiency

[Dennis]

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.

[paii, Ron]

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.

[Tom van Stiphout]

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.