strange query sort behaviour

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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...

Allen Browne said:
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

Reply to group, rather than allenbrowne at mvps dot org.

brettn said:
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?
Click to expand...
Click to expand...
 
brettn said:
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...
Click to expand...


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.
 
Back
Top