Form and query ignore table index

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have created a table that is indexed on 3 columns:
TestNumber(Asc), Category(Asc), TestRunNumber(Desc). The
idea behind having the last segment descending is that I
can run a 'first' query against the table and just see the
latest test run. But, when I run such a query, or view
the data in a form, I never see the latest run first. If
I look directly into the table ... there is the data,
exactly as I expect to see it. But not when I query or
use a form. After I compact the DB, then the form and
query will show the data properly, but not until then.

Why does this happen?

jeff
 
I have created a table that is indexed on 3 columns:
TestNumber(Asc), Category(Asc), TestRunNumber(Desc).
Click to expand...

Tables "have" indexes - but if you're assuming that the table "is
indexed" or sorted in the order specified, you're misunderstanding how
Access handles indexes. Regardless of what indexes exist, the table is
an unordered "bag" of data.
The
idea behind having the last segment descending is that I
can run a 'first' query against the table and just see the
latest test run. But, when I run such a query, or view
the data in a form, I never see the latest run first.
Click to expand...

Do you have an OrderBy clause in the Query? or are you just assuming
that the index will order the data? Because it won't.

Note that the problem is probably due to your (very reasonable!)
misapprehension concerning the First aggregate function. You would
think that it would get the first record in the specified sort order -
BUT IT DOESN'T. For whatever reason, Microsoft set it up so that First
returns the first record *in disk storage order* - an order which is
essentially arbitrary and uncontrollable. If you want to retrieve the
latest run first, the only good way to do so is to use a Subquery
selecting the Max() of the rundate as a criterion.
 
Back
Top