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

  • Thread starter Thread starter Sam Yi
  • Start date Start date
S

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
 
Never make any assumptions about the order in which records will be returned
unless you have an ORDER BY clause on your query.
 
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#
Click to expand...

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.
 
John W. Vinson said:
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.
Click to expand...

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.
 
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.
Click to expand...

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.
 
Back
Top