alter table and order by

[charlie]

Assuming I have a table that has field "ttime" that represents time,
and I want to add a column that contains an index that is corresponds
to sorted ttime. For example

ttime recordnum
-------- -----------------
0.01 1
0.02 2
0.03 3
0.04 4
.... ...


Is "alter table

add column recordnum counter order by ttime" a
valid statement for Access? This seems to work in a VB script, but
does not in Access. What's up with that? I've seen somewhere that
Access uses a different version of Jet than the system uses....could
this be causing a problem?

Another way of performing this might be

select ttime,f1,f2,f3 into [new table] from [source table] order by
ttime;
alter table [new table] add column recordnum counter;

Problem here is that after [new table] is created, altering the table
seems to invalidate the "order by". Hence a "select * from [new table]
order by ttime" gives different results than "select * from [new
table] order by recordnum".

charlie

 

[Marshall Barton]

Assuming I have a table that has field "ttime" that represents time,
and I want to add a column that contains an index that is corresponds
to sorted ttime. For example

ttime recordnum
-------- -----------------
0.01 1
0.02 2
0.03 3
0.04 4
... ...


Is "alter table

add column recordnum counter order by ttime" a
valid statement for Access? This seems to work in a VB script, but
does not in Access. What's up with that? I've seen somewhere that
Access uses a different version of Jet than the system uses....could
this be causing a problem?

Another way of performing this might be

select ttime,f1,f2,f3 into [new table] from [source table] order by
ttime;
alter table [new table] add column recordnum counter;

Problem here is that after [new table] is created, altering the table
seems to invalidate the "order by". Hence a "select * from [new table]
order by ttime" gives different results than "select * from [new
table] order by recordnum".

You are way off track on this. Tables are not sorted
regardless of what you do. Tables are just bags of records
that the db engine can store and retrieve in whatever way
the designers thinks provide the "best" way.

The ONLY way to sort records is by using an ORDER BY clause
in a SELECT query.