Dlookup Formula Alternative?

[Trevor G]

I have created a form which has 28 unbound text boxes where I am using the Dlookup formula to gather totals of one type or another, but I have over a million records to check so it takes a while to populate. I was wondering if there is a quicker alternative. An extract of the forumla's are as follows:

Straight forward totals, average, min, max and counts
=DSum("[Limit]","Combined")
=DAvg("[limit]","combined")
=DCount("[system]","combined")
=DMax("[limit]","combined")
=DMin("[limit]","combined")

And then I am dealing with years
=DSum("[Limit]","Combined","[Underwriting Year]=2009")
=DAvg("[limit]","combined","[Underwriting Year]=2009")
=DCount("[system]","combined","[Underwriting Year]=2009")
=DMax("[limit]","combined","[Underwriting Year]=2009")
=DMin("[limit]","combined","[Underwriting Year]=2009")

I repeat these for 2010 and 2011, then I use the same formula for different fields.

The form has each grouped and colour coded to act like a dashboard and looks nice, but would like to see if it can be populated quicker

Any suggestions would be appreciated

 

[Galaxiom]

Use an aggregate query as the recordsource and bound textboxes.

 

[Trevor G]

Thanks for the suggestion, I can't see how I can have different values for each year and for each field in a single query using groups etc.

Any other suggestions based on my example?

 

[Trevor G]

I can work on this, I realised I can use an iif Statement to select the year then use the field name and use the group to give me the totals, like Sum, Average, Min, MAx etc.

I appreciate the suggestion and will take this further now, again thank you GalaxiomAtHome .

We have a bank holiday over here and if the same where you are I hope you have a very pleasant break.

 

[Galaxiom]

Group By UnderwritingYear then apply the Max, Min, Average and Count to Limit field.
This will give you the annual aggregate values.

Is there a reson you are applying the Count to the System field? This implies that there are null values in that field which will be ignored. If you just need a count of the records use:
Count("*", "combined")

This is faster because Access does not need to test the field for Nulls.

Get the All Time values by using Max, Min, Average and Sum expressions applied to the Max, Min, Average and Count fields respectively as the control sources for textboxes in the footer of the form.

BTW: A field can appear both in a Group By and the totals columns of an aggregate query.

 

[evanscamman]

Allan Browne's 'eLookup' function can be must faster than dLookup. Some people don't agree with this, but in my case it made things run 2 to 3x faster.

 

[Galaxiom]

Domain functions are useful tools to return a single value to one textbox where the data is not from the form's recordset. For examle, summary information about the newest values in various tables. However they are frequently overused, particularly by new developers because they look like a panacea to every problem.

Often developers will use D functions unnecessarily even when a subform's recordset holds the data to be queried. I have had exchanges with developers here who continue to naively insist the D function is a better option even after a detailed explanation of their limitations.

The problem with any Domain function (including ELookup) is what underlies the simple exterior. Each time one is used it essentially entails a connection and query. Multiple domain functions each do this independently hence the inordinate time forms with multiple D functions take to load.

Although it appears simple to get the value with the D function, it is inefficient. I have changed multiple DLookUps to queries in forms that were taking twenty seconds to load, resulting in virtually instant loading.

Instead of the D functions, the aggregate should be calculated in a textbox in the footer of a subform. Then a textbox on the main form uses a control source that refers to the subform textbox. Unlike the D function, this approach doesn't require the memory and time to construct another recordset for each textbox.

A query will return the results in one hit on the database and will almost invariably be faster, particularly on aggregates where the entire recordset must be queried. One exception is the DLookUp where the result lies early in the table. A DLookUp abandons the embedded query when a result is found because it only retuns a singe value while a normal query reads to the end because it returns every value that matches the condition.

However where more than one or two values unrelated to recordsets in the form or subform are required, rather than multiple D functions, it is better to add a subform with the required recordset and show the summaries there. The borders, selector and other extraneous elements of the subform can be removed and the subform will appear to be part of the mainform.

Using a D function inside an expression in a query should be avoided at all costs because each record will call the function.

Also try to avoid nested D functions. They are not only clumsy to write but slow because they are treated as independent queries. A normal query will be optimised by Jet before execution.