Speed up report.

[pennwhite]

X-Posted to MS.pub.Access

In a thread of this same name, there was talk about a 'secondary index'
on a table. I don't have much experience with secondary indexes and I
don't know if they would help in this case but I'm hoping for some
ideas.

I have a 'Bookings' report that shows the number of Bookings per
Location per month. It is grouped by Group and by individual Locations
within each Group. The report speed was acceptable until I added (at
the client's request) a Total Number of Occupancy Days per Location per
month (TotOccupDays_L) and the Total Number of Occupancy Days per
Group per month (TotOccupDays_G).

TotOccupDays_L.ControlSource=DLookUp("OccupDays","Q_Bookings_OccupDays","Lo­cationID="
& [LocationID])
Running Sum=No

TotOccupDays_G.ControlSource=DLookUp("OccupDays","Q_Bookings_OccupDays","Lo­cationID="
& [LocationID])
Running Sum=Over All

Due to the DLookUp, the report is now unacceptably SLOW. It takes
several minutes to complete.

I'd like to get rid of the DLookUp but I'm not sure how to go about it.


Any ideas greatly appreciated.

Penn

 

[Duane Hookom]

Is Q_Bookings_OccupDays the record source of your report? Is there a reason
why you wouldn't use a control source like:
=Sum(OccupDays)

--
Duane Hookom
MS Access MVP
--

X-Posted to MS.pub.Access

In a thread of this same name, there was talk about a 'secondary index'
on a table. I don't have much experience with secondary indexes and I
don't know if they would help in this case but I'm hoping for some
ideas.

I have a 'Bookings' report that shows the number of Bookings per
Location per month. It is grouped by Group and by individual Locations
within each Group. The report speed was acceptable until I added (at
the client's request) a Total Number of Occupancy Days per Location per
month (TotOccupDays_L) and the Total Number of Occupancy Days per
Group per month (TotOccupDays_G).

TotOccupDays_L.ControlSource=DLookUp("OccupDays","Q_Bookings_OccupDays","Lo­cationID="
& [LocationID])
Running Sum=No

TotOccupDays_G.ControlSource=DLookUp("OccupDays","Q_Bookings_OccupDays","Lo­cationID="
& [LocationID])
Running Sum=Over All

Due to the DLookUp, the report is now unacceptably SLOW. It takes
several minutes to complete.

I'd like to get rid of the DLookUp but I'm not sure how to go about it.


Any ideas greatly appreciated.

Penn

 

[Penn]

Hi Duane,

No, the record source for the report is a crosstab query that splits the
occupancy into days of the month. OccupDays is not in the report record
source.

It's sort of like this and the last (Totals) column is the one I need to
generate.

Report for January
1 2 3 4 ... 31 Totals
Group: West Coast 50 40 50 60 60 260
San Leandro, CA 25 10 10 20 15 80
Ojai, CA 25 30 40 40 45 180

Group: Northwest 30 30 50 40 60 210
Salem, OR 15 15 15 20 20 85
Beaver Falls, WA 15 15 35 20 40 125

Totals 80 70 100 100 120 470

 

[Duane Hookom]

You can generate the Totals column in your crosstab. I haven't seen anything
that suggests the need for either a running sum or a DLookup().

--
Duane Hookom
MS Access MVP
--

Hi Duane,

No, the record source for the report is a crosstab query that splits the
occupancy into days of the month. OccupDays is not in the report record
source.

It's sort of like this and the last (Totals) column is the one I need to
generate.

Report for January
1 2 3 4 ... 31 Totals
Group: West Coast 50 40 50 60 60 260
San Leandro, CA 25 10 10 20 15 80
Ojai, CA 25 30 40 40 45 180

Group: Northwest 30 30 50 40 60 210
Salem, OR 15 15 15 20 20 85
Beaver Falls, WA 15 15 35 20 40 125

Totals 80 70 100 100 120 470

Is Q_Bookings_OccupDays the record source of your report? Is there a
reason why you wouldn't use a control source like:
=Sum(OccupDays)

--
Duane Hookom
MS Access MVP
--

X-Posted to MS.pub.Access

In a thread of this same name, there was talk about a 'secondary index'
on a table. I don't have much experience with secondary indexes and I
don't know if they would help in this case but I'm hoping for some
ideas.

I have a 'Bookings' report that shows the number of Bookings per
Location per month. It is grouped by Group and by individual Locations
within each Group. The report speed was acceptable until I added (at
the client's request) a Total Number of Occupancy Days per Location per
month (TotOccupDays_L) and the Total Number of Occupancy Days per
Group per month (TotOccupDays_G).

TotOccupDays_L.ControlSource=DLookUp("OccupDays","Q_Bookings_OccupDays","Lo­cationID="
& [LocationID])
Running Sum=No

TotOccupDays_G.ControlSource=DLookUp("OccupDays","Q_Bookings_OccupDays","Lo­cationID="
& [LocationID])
Running Sum=Over All

Due to the DLookUp, the report is now unacceptably SLOW. It takes
several minutes to complete.

I'd like to get rid of the DLookUp but I'm not sure how to go about it.


Any ideas greatly appreciated.

Penn

 

[Penn]

You're right, of course. I just didn't know how to do it but I figured it
out after you said it was possible. So that shaved almost a minutie off the
report generation time. The query takes 5 seconds to run and the report is
still taking a minute at least. I'll start looking at the report and see if
I can track down the problem(s).

Thanks very much for your help.

Penn.

You can generate the Totals column in your crosstab. I haven't seen
anything that suggests the need for either a running sum or a DLookup().

--
Duane Hookom
MS Access MVP
--

Hi Duane,

No, the record source for the report is a crosstab query that splits the
occupancy into days of the month. OccupDays is not in the report record
source.

It's sort of like this and the last (Totals) column is the one I need to
generate.

Report for January
1 2 3 4 ... 31 Totals
Group: West Coast 50 40 50 60 60 260
San Leandro, CA 25 10 10 20 15 80
Ojai, CA 25 30 40 40 45 180

Group: Northwest 30 30 50 40 60 210
Salem, OR 15 15 15 20 20 85
Beaver Falls, WA 15 15 35 20 40 125

Totals 80 70 100 100 120 470

Is Q_Bookings_OccupDays the record source of your report? Is there a
reason why you wouldn't use a control source like:
=Sum(OccupDays)

--
Duane Hookom
MS Access MVP
--

X-Posted to MS.pub.Access

In a thread of this same name, there was talk about a 'secondary index'
on a table. I don't have much experience with secondary indexes and I
don't know if they would help in this case but I'm hoping for some
ideas.

I have a 'Bookings' report that shows the number of Bookings per
Location per month. It is grouped by Group and by individual Locations
within each Group. The report speed was acceptable until I added (at
the client's request) a Total Number of Occupancy Days per Location per
month (TotOccupDays_L) and the Total Number of Occupancy Days per
Group per month (TotOccupDays_G).

TotOccupDays_L.ControlSource=DLookUp("OccupDays","Q_Bookings_OccupDays","Lo­cationID="
& [LocationID])
Running Sum=No

TotOccupDays_G.ControlSource=DLookUp("OccupDays","Q_Bookings_OccupDays","Lo­cationID="
& [LocationID])
Running Sum=Over All

Due to the DLookUp, the report is now unacceptably SLOW. It takes
several minutes to complete.

I'd like to get rid of the DLookUp but I'm not sure how to go about it.


Any ideas greatly appreciated.

Penn