strange query sort behaviour

access 2003 problem

SELECT * INTO [test_table] FROM [old_table] ORDER BY [PH_ORDER];

(PH_ORDER is a unique numeric field)

I thought this would be straight forward, but in some cases (particularly 
tables > 10,000 records) the resulting table will have some blocks of records 
(about 20) out of order. Typically when this occurs the table when opened 
will start with ,say, records 40-60 and then go back to 1.

Does anyone have an explanation as to why this might be happening?

0
Utf
10/15/2007 1:20:00 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
681 Views

Similar Articles

[PageSpeed] 40

Try a compact/repair, in case there is a damaged index:
    Tools | Database Utilities | Compact/Repair
or in Access 2007:
    Office Button | Manage | Compact/Repair

If you open the table in design view, are you dead certain this is a Number 
field (not a Text field)? A Text field could have a leading space on those 
problem records. (Text fields are very common for non-math data such as 
phone numbers and zip codes.)

Does it only happen in the Make Table query? If so, it probably doesn't 
matter, since your new table will sort correctly anyway.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"brettn" <brettn@discussions.microsoft.com> wrote in message
news:7B393E6E-2598-4159-8503-BF0BDB782344@microsoft.com...
> access 2003 problem
>
> SELECT * INTO [test_table] FROM [old_table] ORDER BY [PH_ORDER];
>
> (PH_ORDER is a unique numeric field)
>
> I thought this would be straight forward, but in some cases (particularly
> tables > 10,000 records) the resulting table will have some blocks of 
> records
> (about 20) out of order. Typically when this occurs the table when opened
> will start with ,say, records 40-60 and then go back to 1.
>
> Does anyone have an explanation as to why this might be happening? 

0
Allen
10/15/2007 1:31:34 AM
brettn wrote:

>I've seen this happen numerous times (10+) with compacted / repaired files as 
>well.
>
>We are using a printing / merge program that attaches directly to the access 
>table, and so if the table is out of order our printing will be as well.
>
>I need to 'sort' this probelm somehow...


Write "Tables are never sorted" 100 times.

Now that you understand the situation, create a query that
sorts the records and change the merge process to use the
query.

I don't really mean to be so blunt, but you need to think of
records in a table as if something just dumped them in a
bag.  Relational database engins are free to store the
records in whatever way the system developers thought would
be the most "efficient". (And who knows what "efficient"
meant to them at the time when they wrote the code to save
and retrieve records).

Displaying a table in sheet view is just asking Access to
use an internally generated form with a default query to
display the records, which has no reliable relationship to
how they are stored and no guaranteed relationship to how
your merge process will retrieve them.  The ***ONLY*** way
to sort records is via a query.

-- 
Marsh
MVP [MS Access]
0
Marshall
10/15/2007 3:33:36 AM
Reply:

Similar Artilces: