Report that never runs...

[btothap]

Dlookup in function, is there a way around?

Hiya.
First off i'm in access02, I have a big nasty query that pulls data from local tables as well as a remote table elsewhere on the lan. The remote table is in access97 and is generated by our phone system reporting software and can't be modified. When running the report(or query for that matter) with the phone system data included, it can take up to several hours to run depending on the time of the month. If I strip away the phone data, the report/query will run in under a minute. Recently I've had to add vb script to the report that performs dLookups on another table. With that function in place the report will not run, period. I've left it for 36 hours and it will sit idle with the status bar full of little blue dots.

I guess my question is really about optimization and why the insane slowness. I've tested the network and speed shouldn't be an issue there. I can run the reports from a third location, accessing both tables remotely, and find the same results. I limit the recordset in the phone table with a query, prior to the one that feeds the report, but it doesn't alter the horrendous run time. I know this is probably an impossible question to answer, but any help would be greatly appreciated.

Sorry for being so long winded.

-shane.

 

[Pat Hartman]

First of all get rid of the DLookup()S. They are very inefficient in this context. You just need to add the lookup tables to your query and join them using a left join. That way all the columns you need will be in the report's RecordSource.

Make sure that your database is compacted regularly to reduce its size and update the statistics Jet keeps on the table contents.

Sometimes adding an appropriate index will speed up selection.

Make sure your queries only select the columns they need and do not include any order by clauses. The report needs to control detail row order so you can use the report's sorting and grouping dialog to specify sort sequence.

 

[btothap]

Hi Pat, thanks for your help.

I tried to find a way around using the DLookups, because as i've read in your posts before, they're slow, but in this application they seemed like the only route. Basically for each record I need to calculate based on totals from other records, for example if joe salesman is ranked #6 on his profits for the month, i need a calculation that will show the dollar amount necessary to move him up to #3. Here's the function i'm using.

Private Function gprank()

Dim rankadj
rankadj = Me.mugp_rank - 3

If Me.gpranknum = 1 Then
gprank = "Leading By " & Me.gpTtl - DLookup("[MU_GP]", "ranking_query", "[mugp_rank] = 2")
ElseIf Me.gpranknum = 2 Then
gprank = DLookup("[MU_GP]", "ranking_query", "[mugp_rank] = 1") - Me.gpTtl & " Moves you to #1"
ElseIf Me.gpranknum = 3 Then
gprank = DLookup("[MU_GP]", "ranking_query", "[mugp_rank] = 1") - Me.gpTtl & " Moves you to #1"
Else
gprank = DLookup("[MU_GP]", "ranking_query", "[mugp_rank] = " & rankadj & "") - Me.gpTtl & " Moves you up 3 places"
End If

End Function

Where gpranknum is the individuals ranking
gpTtl is the profit for the individual

If I can do this without the DLookups i'd be very grateful if you could point me in the correct direction as to how since i have to calculate on records other than the current one.

Thanks again for your help.

-shane