Large database issues

[Floydene]

I am working with an Access file that contains over 3 million records. I am
using Access 2007 and when I originally began working with the file I asked
for totals at the bottom of the table to verify I had all the information.
But now when I open the database (I have several queries now), the word
'Calculating...' appears at the lower left corner of the status bar. I have
tried turning off the totals, but all the queries are still running very
slowly.

Is this the result of the database having such a large number of records or
the totals or both? Is there a way to get it running more efficiently? Our
computer runs on Windows XP Home.
Thanks

 

[John W. Vinson]

I am working with an Access file that contains over 3 million records. I am
using Access 2007 and when I originally began working with the file I asked
for totals at the bottom of the table to verify I had all the information.
But now when I open the database (I have several queries now), the word
'Calculating...' appears at the lower left corner of the status bar. I have
tried turning off the totals, but all the queries are still running very
slowly.

Is this the result of the database having such a large number of records or
the totals or both? Is there a way to get it running more efficiently? Our
computer runs on Windows XP Home.
Thanks

Well, the computer will need to sum three million values, and re-sum them
every time you make a change. Even with a fast computer this will take time!
How is the sum calculated? It's certainly not "at the bottom of the table"
since Tables don't do calculations, and how does having a sum help validate
the data?

Do you have Indexes on the fields used for sorting or for criteria? When you
open the database, are you automatically opening a form, or running some
queries?

 

[Floydene]

The sum is calculated by the totals function - it shows up on the bottom of
the screen in table view. It validates the data becuase I began with two
tables and merged them into one. I knew the total of each table, so wanted
to ensure that the merge included all the data from each original table.

I do not have indexes on any of the fields. I am not familiar with Indexes,
so will look that up to see if that might help. When I open the database I
just have a list of queries along with the table. It's when I go to run a
query that my system gets bogged down. I suspect that some better
organization with the data will help.

Thanks for the quick reply.

 

[John W. Vinson]

The sum is calculated by the totals function - it shows up on the bottom of
the screen in table view. It validates the data becuase I began with two
tables and merged them into one. I knew the total of each table, so wanted
to ensure that the merge included all the data from each original table.

I do not have indexes on any of the fields. I am not familiar with Indexes,
so will look that up to see if that might help. When I open the database I
just have a list of queries along with the table. It's when I go to run a
query that my system gets bogged down. I suspect that some better
organization with the data will help.

It sounds like you're using 2007? Tables should generally NOT be used for
interaction with data; and it sounds like your total really should be a
one-time calculation after loading the data, not an ongoing time-eater.

Consider using Forms displaying a subset of the data, rather than running lots
of queries all the time; Action Queries (maketable, append, delete) queries
have their place but I cannot imagine routinely having them run whenever a
database is opened!