Access 2007 won't sort properly

  • Thread starter Thread starter Keith Lawrence
  • Start date Start date
K

Keith Lawrence

There is a problem I have not been able to solve when it comes to queries in
2007. I've done simple tests on a couple of machines with Access 2007
installed and the results are close. The test I used is a make table query
that pulls data from a table with one field that has integer values in it.
They go from 1-3000 with no duplicates. I built 10 different tables with a
sort that puts the integers in descending order. When I checked the results,
1 to 3 of those tables were not in order. I find it of great concern that
anywhere between 10% and 30% of the queries run are not giving the proper
results.

I have run every diagnostic that Microsoft provides and I have the most
current updates and service packs on Microsoft Office. I have restarted the
system, compacted and repaired the database, and started new databases and
yet the problem persists. I am about to uninstall 2007 and go back to Access
2000. I just wanted to see if anyone else had the problem and knows how to
fix it.
 
On Tue, 17 Jun 2008 07:12:01 -0700, Keith Lawrence <Keith
(e-mail address removed)> wrote:

Perhaps it's a misunderstanding. Tables are NOT sorted. Of course when
you view a table, Access has to present the data in some order, and
typically uses the PK (you *do* have a PK, right?).
If you want to present the data in a sorted fashion, you have to be
explicit and use the ORDER BY clause. Then things will go back to 100%
reliable.

-Tom.
 
Never assume anything about the order of rows in a table. Under relational
database theory, the order of rows is irrelevant.

If you need the data presented in a specific order, use a query with an
appropriate ORDER BY clause.
 
The table does have a primary key, yet the failure rate persists. The order
by clause does have a value that tells it to sort in descending order. If
the Make Table Query cannot create tables where the records are sorted in the
order specified, what is the point of having sorting capability on Make Table
Queries?

For what I do, it is very important that tables are built with a preserved
record order. This is because the files are exported to use with other
software and other machines. Access 2000 could do this reliably, Access 2007
cannot. I was hoping to find a setting somewhere that would enable 2007 to
have the accuracy of 2000.
 
Keith said:
The table does have a primary key, yet the failure rate persists.
The order by clause does have a value that tells it to sort in
descending order. If the Make Table Query cannot create tables where
the records are sorted in the order specified, what is the point of
having sorting capability on Make Table Queries?
Click to expand...

There is no point other than the sort option is there for other queriy types
and it would have been extra work to disable it based on the query type.
For what I do, it is very important that tables are built with a
preserved record order.
Click to expand...

No, it is not. You only think that it is.
This is because the files are exported to
use with other software and other machines.
Click to expand...

So export a query based on the table instead with an explicit sort order.
Access 2000 could do
this reliably, Access 2007 cannot. I was hoping to find a setting
somewhere that would enable 2007 to have the accuracy of 2000.
Click to expand...

You got lucky if this worked reliably for you in Access 2000. An exported
table simply does not have a reliable order for the output.
 
Back
Top