Help, Sorting Query bug

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Hello, for the last two years we've been experiencing
problems with a very simple query that sorts a table and
makes a new sorted table. Once the table exceeded 100,000
records the resulting sorted table was not always sorted
correctly.

Today, I had to run the sorting query 6 times to finally
get a correctly sorted table. (access 2002, SP3 on windows
XP). About 25% of the time the table will sort correctly.

Our table has now grown to 415,000 records.

Thanks for your assistance
 
sorts a table and
makes a new sorted table.
Click to expand...

That's your problem. There *is* no such thing!

A Table HAS NO ORDER. It's an unsorted "bag" of data!

If you want to see records in a particular order, you must - NO
OPTION! - use a Query as the recordsource for the report or form or
export, sorting by the desired field. If that field is indexed even a
half-million row table should be displayed in order with decent
performance.
 
The problem is that when records are created there is no guarantee that they
will be stored in a certain order. Since you can get them in order with the
simple query I would suggest that you include the sort fields in your new table.
Then you can use a query to sort the new table when you need to have it in some order.

By the way, why are you creating the table at all. Normally, you can use the
query you have to display the records. And if it is a "simple" query, then it
is probably updatable.
 
Back
Top