"Danny" <da********@hotmail.com> wrote ...
I was trying to tihnk of solutions to the table in access being out of order
I assume that by 'table in access' you mean a table in a Jet database.
The physical (e.g. on disk) ordering of a database table is determined
by its clustered index. The performance advantage of the clustered
index (physical order) is obvious e.g. think of how a paper copy
telephone directory is ordered by last name, first names.
The clustered index is a fundamental concept of table design which Jet
does not expose to the user e.g.
CREATE CLUSTERED INDEX my_index ON MyTable (MyKeyColumn)
will fail with a syntax error, remove the CLUSTERED and it is OK.
However, Jet tables do of course have a clustered index, being the
primary key (PK). This is why an IDENTITY/autonumber (or a homemade
incrementing integer) column is never a good choice for PK; imagine
now a telephone directory ordered by telephone number. If the IDENTITY
column is required to enforce uniqueness for the sake of it, define
your PK column as (meaningful_text_column, IDENTITY_column).
Also bear in mind that a Jet clustered index is only rebuilt when the
database file is compacted. Any rows added to the table subsequent to
the last compact will be added unordered to the 'bottom' of the table.
Jamie.
--