Sort order in query based on ID (Autonumber)

[Blueskies]

Hi there

This is something I occasionally see in Access and has been bugging me for quite a while.

As an example, when I have a table (all text fields except for the ID field which is an Autonumber with a unique index - ie just what Access creates when you import data) and I try to make a new table from a query by indexing the Autonumber field in descending order (ie to reverse the order of the table), it doesn't work properly.

So if I have:

SELECT [mytable].* INTO [mytable sorted] FROM [mytable] ORDER BY [mytable].[ID] DESC;

When I preview the data (ie run the select query to have a look at it), it looks fine.

When I change the query to a 'Make Table' and I then I check the table it makes, the order changes part-way down the list, so looking at the ID field it runs from number 2669 down to 2087 correctly, then it goes from 1960 to 1956, then 1803 to 1799, then 1751 to 1747, etc etc etc. After a while it seems to correct itself again, and orders normally down to #1

Can anyone suggest what's going on? - it only happens sometimes, not always, so it's even more annoying than if it was happening constantly.

I'm using Access 2002.

Thanks

 

[spikepl]

Tables have no inherent order! The order you "see" can change by itself-. Always use queries and specify ORDER BY, if order is of any significance.

 

[Blueskies]

So there's no way to create a table from a query that's in a specific order?

Surely there must be - or else why even have the ORDER option?

 

[namliam]

the order option is what Spike means if it means anything... anytime you look at any table WITHOUT a spefic order by on it (i.e. just in table view) the record order is/can be random unless you specificaly use a query to order by or use the sort option in table view....

 

[Blueskies]

OK guess I'll just have to accept that then.

Thanks for the replies guys.

 

[Blueskies]

Update in case it helps anyone - memo fields affect sort order dramatically. Removing them seems to help, thought I had none of them in my original query.