Query does not calculate/show all results

[alex]

I have a query (query1) that is linked to two other queries. When I
run query1 it executes very quickly. When I view the results in
Datasheet View, however, the count (record x of x) fails to populate at
the bottom. Also, when I use the scroll bar to scroll to the bottom,
the bar immediately jumps up as more records seem to be calculated;
i.e., a typical query of 12000 plus records will usually yield a tiny
scroll bar, this query, however, yields a long scroll bar. I fail to
understand why this query acts like this.

The reason I ask this question is because I have other queries tied to
query1 and they're incredibly slow. I think it's because of the
problem I referenced above. I've tried analyzing and compacting the
database; nothing seems to make these queries run faster and query1
function normally.

Any help would be appreciated. I hope I've explained this problem
accurately.

 

[blgilbert]

This behavior is by design. It keeps Access from having to return all
records at once, which would make things seem much slower.

The first thing that comes to mind to improve performance is to look at
the indexes on the source tables. You might want to add an index for
the fields on each table that participate in a join or are used for
query criteria.

I also assume that your join cardinalities are correct.

HTH,
Barry

 

[Guest]

Although the first query opens quickly in datasheet view its result set is
not yet completely filled. This is why the row count fails to update as soon
as you see the first subset of the total rows. The other queries which call
upon this query require its result set to be completely filled, and are
consequently slow to open.

Ken Sheridan
Stafford, England