Dlookup Formula Alternative?

[healey21]

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

[SydneyGeek]

Any reason why you couldn't use a Totals query behind the scenes, and populate the dashboard controls? I'd be surprised if that was slower than a bunch of lookups.

Denis

[healey21]

Thanks Denis,

I am looking at that but can't see how I could get different values based on different years in the same query. Is that possible from the sample I posted?

[healey21]

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 Denis.

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

[SydneyGeek]

Not directly, you will need to combine a few queries; but before going too far, will this data be changing often?
The reason I ask, is that you could use code to loop through a series of Totals or crosstab queries, pushing the results to a table that you use as the basis for the dashboard.

EDIT: Answers crossed during posting...

Hopefully you can get it to work next week. Enjoy the weekend and if you get stuck, post back.

Denis

[Norie]

If you want separate totals/averages etc by year just group by year.

[healey21]

Thanks Denis,

I have created the over all totals etc so far and the 2009, 2010 and 2011 for the fields Limit and Excess I will now work on the others, intotal 120 Text fields to be populated, so maybe some kind of VBA maybe the answer.

The data comes 4 different systems and is refreshed once a week.

Hi Norie,

Thank you for the suggestion and I am working on this.

The list of fields I am working with are:

Limit, Excess, Limit GBP, Excess GBP, 100% GROSS EPI, Our EPI GBP, Net EPI GBP All fields come from one table called Combined, where I need totals, average, min, max and count overall and then broken down by year, 2009, 2010, 2011.

I would consider any option if it speeds things up. I had considered VBA to do the calculations and place into a temp table, but not sure where to start and conclude.

I hope you both have a peaceful weekend.

[Norie]

Is there a date field?

[healey21]

Yes Norie,

It is Underwriting Year, which holds the year only.

[Norie]

Well group by that field and you should get separate results for each year.