Somthing un-expected happen when do "Compact and Repair Database"

[Sam Yi]

After I did "Compact and Repair Database" to my MDB, I found the reulst order
for query has been changed.
Script: SELECT a.* FROM tblFundPrices a WHERE a.FundNumber = 'V81A' AND
a.NAVDate <= #07/31/2009#

From some other post I know the "compact" operation will "reindex the
indexed fields, so if you have a corrupted index, this should fix it"
But I have no idea how does this work, also I am not sure my data in table
tblFundPrices has index problem.

The definition for table tblFundPrices:
Column DateTyoe
NAVData Date/Time
FundNumber Text
NAV Number
Dist Number
TNA Number
Basis Text
Primary key: NAVDate,FundNumber,Basis
Indexed:NAVDate,FundNumber

Thanks in advance for any comment

Sam

 

[Douglas J. Steele]

Never make any assumptions about the order in which records will be returned
unless you have an ORDER BY clause on your query.

 

[John W. Vinson]

After I did "Compact and Repair Database" to my MDB, I found the reulst order
for query has been changed.
Script: SELECT a.* FROM tblFundPrices a WHERE a.FundNumber = 'V81A' AND
a.NAVDate <= #07/31/2009#

Since your query does not contain an ORDER BY clause, you cannot expect
anything about the record order. Access will return records in whatever order
the query optimizer finds most convenient. I'm not at all surprised that a
compact might change this.

If you want records in a particular sequential order - include that in your
query. Otherwise it's uncontrolled and unpredictable.

 

[Sam Yi]

Since your query does not contain an ORDER BY clause, you cannot expect
anything about the record order. Access will return records in whatever order
the query optimizer finds most convenient. I'm not at all surprised that a
compact might change this.

If you want records in a particular sequential order - include that in your
query. Otherwise it's uncontrolled and unpredictable.

Actually, even if I add ORDER BY NAVData clause, the result order are still
different.
And only once I add ORDER BY NAVData,basis. the result order will be same.

 

[John W. Vinson]

Actually, even if I add ORDER BY NAVData clause, the result order are still
different.
And only once I add ORDER BY NAVData,basis. the result order will be same.

Could you post the SQL view of the query, the datatypes of the relevant
fields, and perhaps a sample of a few rows? This isn't making much sense to
me.

Might the FundNumber field be a Lookup field? If so it will sort by the
(hidden) numeric ID, the actual content of the table, not the visible text.

 

[AccessVandal via AccessMonster.com]

Maybe Access got confused, it seems that it is try to link to another table
in your DB as your SQL syntax show that you had a "a.*" with the table
"tblFundPrices a". Somewhere along the way compact and repair causes a
problem. Maybe it's looking for the missing table index but that's my guess.

You might want to try and remove the "a", since you're only using one table
in your SQL string and do a compact and repair again. Is there a need for the
alias?

 

[AccessVandal via AccessMonster.com]

PS.
If you using "Order By NavDate", you need to remove the alias. If you use the
alias, it should be "Order By a.NavDate" with the alias name "a".

After I did "Compact and Repair Database" to my MDB, I found the reulst order

snip