Sum large recordsets in a form..?

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

 

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

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]

 

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

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]

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

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

Kent J.