performance issue in form

[Guest]

I have a table which has index on id and Amount field (table 1). The table1
has more than 20,000 records and will keep increasing. There is another table
table2 which is related to table1 by id.
I have created a form based on the query which links table1 and table2 by id
and on the form footer I have a unbound text field which calculates the sum
of amount.
Sometimes it takes 5 to 10 seconds to calculate sum, sometimes it takes for
ever to calculate the sum. The performance keeps changing
I tried removing the index in table1 also but there is no improvement in
performance.
But I had a old database which is doing the same task and it populates the
sum on loading the form immediately.
Can some one help on what I am missing?
Any help would be greatly appreciated.

Thanks & Regards
Kowsy

 

[Guest]

You should have an index on both ID fields.
You probably don't need an index on the Amount field...unless you have any
queries that pull out records by their value.

Have you tried opening the query? Is it slow?

What is the formuls for your calculation?

Steve