Suggestions to resolve slow query

[David Mitchell]

I need to have a form which will display all products (from
tblproducts) and their stock level (sum of product received from
tblacqdetail, minus sum of product sold from tblinvdetail) in a
listbox. The form should only display those records whose product code
contains the characters typed in a text box on the form.

At the moment I do this by using the on change event of the text box
and refreshing the listbox.

The queries are a subquery to get stock level and then main query which
contains the criteria : - "like *mytextfield*"

This works as it should but takes forever to load and to use,
particularly as the on change coding refreshes the list box on each
keypress!

Can anyone suggest either a way to speed up the existing method or
point me in the right direction of an alternative.

Thanks

[Allen Browne]

Sheesh, David, you are asking Access to perform a series of calculations
involving rows of data from multiple tables, using wildcard matching,
between keystrokes?

Suggestions:

1. Would you consider using the AfterUpdate event of the search box so it
does not have to recalculate with every keystroke?

2. Could you live with just the trailing wildcard in the criteria, i.e.:
Like [mytextfield] & "*"
That will permit JET to use an index on the field.
Of course, this presumes that:
a) the criteria is applied against a field of type Text, and
b) the field is indexed.

3. How are the calculations performed? In general:
- Subqueries will be fastest.
- Built-in functions are second best.
- User-defined function are third best.
- Domain-aggregate functions are slowest.

4. There may be other ways to optimise this, such as:
- stacked queries
- temp tables

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David Mitchell" <da**************@talk21.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

>I need to have a form which will display all products (from
tblproducts) and their stock level (sum of product received from
tblacqdetail, minus sum of product sold from tblinvdetail) in a
listbox. The form should only display those records whose product code
contains the characters typed in a text box on the form.

At the moment I do this by using the on change event of the text box
and refreshing the listbox.

The queries are a subquery to get stock level and then main query which
contains the criteria : - "like *mytextfield*"

This works as it should but takes forever to load and to use,
particularly as the on change coding refreshes the list box on each
keypress!

Can anyone suggest either a way to speed up the existing method or
point me in the right direction of an alternative.

Thanks

[David Mitchell]

Thanks for responding Allen. Your site has been a great help to me in
this (and previous) project(s). I did realise that I was asking a lot
of Access but my boss wanted it to work this way. It has a lot to do
with the system that is being replaced, it used stored stock values and
was much faster (although you needed to know exactly what the stock
code was - hence the "like * mytextfield*")

The criteria field is text based and I could live with myfield & "*"
I'll need to check whether or not it is indexed. The after update
would probably be okay bearing in mind this is still a major
improvement on the old system.

I'll try both of these on Tuesday when I am back in the office and let
you know how it goes.

David