Access Query with relationships Too Slow

[Ian W.]

Hello All,

I've been waiting for a response to my query for over 30 mins... I'm
attempting to run an append or make-table query for roughly 800,000 rows of
data. I believe that it is taking so long due to the fact that I'm trying to
return a shipping rate column onto my existing data file based on 4
relationships to another table (weight, service, and zone). I've already
tried the 'compact & repair function', restarting the computer to clear the
cache, and linking to the existing data files and creating a fresh database.
As of now, my two tables are in a single database and the size is slightly
over 750 MB.

Any suggestions to improve??

Thanks very much.

Ian W.

 

[Phil Smith]

Tweak it so that instead of running for 800,000, it runs for 8. or 80.
or 800. Try to get an idea of how long it takes per record, so you
can extrapolate how long it would take to 800,000. Work with a make
table query, as perhaps you have something going on that is causing it
to create more records than you think.

If you are running this from a network drive, try opening it in exlusive
mode. Record locking can slow things down, and that many records can
easily exceed the maximum number of record locks available.

 

[Ian W.]

I forgot to mention that I have already tried turning off the 'autocorrect'
feature, and that it did not solve the problem.

Thanks gain,

Ian

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too. Also any indexes