Sum large recordsets in a form..?

K

Kent J

Hi all,

I have a recordset that's 15 000 records in a form.
The reason that I want so many records is beacase I want to use filters.
This works fine.
But if I try to summarize 15 000 numeric values it takes very long time
(1 minute). I have tried: Me.ControlSource = sum([Value1])
Is there a better and faster way?

One idea is to generate SQL-code from the filters.

Kent J.
 
J

John W. Vinson

Hi all,

I have a recordset that's 15 000 records in a form.
The reason that I want so many records is beacase I want to use filters.
This works fine.
But if I try to summarize 15 000 numeric values it takes very long time
(1 minute). I have tried: Me.ControlSource = sum([Value1])
Is there a better and faster way?

One idea is to generate SQL-code from the filters.
Click to expand...

Instead, you might want to base the Form itself on a parameter query selecting
only the "filtered" records.

It's also very helpful to be sure that you have indexes on all of the fields
used for either filtering or sorting the records.

John W. Vinson [MVP]
 
K

Kent J

John W. Vinson skrev:
Hi all,

I have a recordset that's 15 000 records in a form.
The reason that I want so many records is beacase I want to use filters.
This works fine.
But if I try to summarize 15 000 numeric values it takes very long time
(1 minute). I have tried: Me.ControlSource = sum([Value1])
Is there a better and faster way?

One idea is to generate SQL-code from the filters.
Click to expand...

Instead, you might want to base the Form itself on a parameter query selecting
only the "filtered" records.

It's also very helpful to be sure that you have indexes on all of the fields
used for either filtering or sorting the records.

John W. Vinson [MVP]
Click to expand...


Is it possible sum up the table directly?
I have unsuccessfully tried:

Me.RecordSource = "select sum(Value) from Table1 where team='" & KmbTeam
& "'"

Kent J.
 
Top