slow moving among records

[morgan]

Hello guys
learning new things on access I keep improving my db, that is my family bank
account. I’ll briefly explain how it is.
In the main form there is a single record of my table, a sub form with 3
pages of graphs and several buttons to open forms of specific categories
(car, children, house and so on). Everything works on queries based on the
selected year. I had all this structure redundant for every year. To optimize
and shrink the db, I changed all the queries in order to pass them the
desired year from a list box. Now the annoying problem is that it takes a
couple of seconds to mouse from a record to another one scrolling the mouse
wheel while the change was very quick before. The table contains around 1000
records over 2 year. What’s happened? This kind of query approach can slow
down my db?
I hope to have been clear enough...still learning english too

Ciao

 

[Arvin Meyer [MVP]]

Make sure that your fields are properly indexed in your table, and do your
searching on indexed fields. You should be able to find a record in
sub-second times, and scroll very quickly.

 

[morgan]

All table fields used to set criteria for the queries are indexed (field
properties-->General tab-->Indexed-->Yes). What else I can do?
The change takes around 1 second, not 2, with hourglass on screen.

 

[Arvin Meyer [MVP]]

Have you built relationships between tables which share a key? That should
speed it up a bit, but it certainly shouldn't be taking that long if indexes
are proper. Have a look at Tony Toews performance page to see if there's
something on it that will help:

Performance:
http://www.granite.ab.ca/access/performancefaq.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[morgan]

I've probably found the cause but not the solution yet.
I've 1 sub form that is a kind of summary by category (8 in total). with
boxes that report the total for each one. Deleting this subform, my problem
is solved.
Here below, what I've done for each category.
Query to extract all the related record (used for another sub form
too)-->Query of the previous query that makes the sum of the amount-->Query
that collects the sum of all categories. used as reference for the summary
sub form.

Is there a more efficient way to do it?

FYI, there is only one table that is my bank statement.

Tks in advance