This is an oldie but a goodie...

[MLH]

Has it been true in all versions of access from 1.0 forward
that if you have a table with primary key [MyID] and other
non-indexed fields [Field1], [Field2]... that using an unsorted
query to display records in that table will not necessarily
result in the dynaset being displayed in keyfield order?

Take this for example...

SELECT DISTINCTROW tblMyTable.MyID, tblMyTable.Tracked
FROM tblMyTable
WHERE ((tblMyTable.Tracked=Yes));

It may or may not display records in the order of the keyfield
[MyID]. This leads one to believe the rec's in dynaset are listed
in the order that they were entered into MyTable. Anyone
know for sure?

I have to add ORDER BY tblMyTable.MyID; if I want
the dynaset listed in ANY table's keyfield order.

[Wayne Morgan]

I don't guarantee this, but I think it is kind of a mix. Compacting the
database will refresh the index on the key field. After a compact, there is
a good chance that if you do no sorting yourself, that it will display in
key field order. Records added after the compact may or may not be in any
particular order. However, there is nothing that requires this to be true,
if you want something sorted, you need to tell it to sort.

--
Wayne Morgan
MS Access MVP
"MLH" <CR**@NorthState.net> wrote in message
news:tl********************************@4ax.com...

Has it been true in all versions of access from 1.0 forward
that if you have a table with primary key [MyID] and other
non-indexed fields [Field1], [Field2]... that using an unsorted
query to display records in that table will not necessarily
result in the dynaset being displayed in keyfield order?

Take this for example...

SELECT DISTINCTROW tblMyTable.MyID, tblMyTable.Tracked
FROM tblMyTable
WHERE ((tblMyTable.Tracked=Yes));

It may or may not display records in the order of the keyfield
[MyID]. This leads one to believe the rec's in dynaset are listed
in the order that they were entered into MyTable. Anyone
know for sure?

I have to add ORDER BY tblMyTable.MyID; if I want
the dynaset listed in ANY table's keyfield order.

[Terry Kreft]

Yes it is true that you should not expect a particular sort order unless you
specify an ORDER BY clause.

(this should be broadly true of any relational database)

--
Terry Kreft
MVP Microsoft Access
"MLH" <CR**@NorthState.net> wrote in message
news:tl********************************@4ax.com...

Has it been true in all versions of access from 1.0 forward
that if you have a table with primary key [MyID] and other
non-indexed fields [Field1], [Field2]... that using an unsorted
query to display records in that table will not necessarily
result in the dynaset being displayed in keyfield order?

Take this for example...

SELECT DISTINCTROW tblMyTable.MyID, tblMyTable.Tracked
FROM tblMyTable
WHERE ((tblMyTable.Tracked=Yes));

It may or may not display records in the order of the keyfield
[MyID]. This leads one to believe the rec's in dynaset are listed
in the order that they were entered into MyTable. Anyone
know for sure?

I have to add ORDER BY tblMyTable.MyID; if I want
the dynaset listed in ANY table's keyfield order.

[MLH]

You guys - thanks for clearing this up for me.
It makes sense when you think about it.

mlh