building a search form

Hi,

This thread is also posted on office development- since I wasn't too sure where to put it- I'd appreciate if you could redirect me about this.

I have a products table with over 50,000 records.

I'd like to build a search form for products, with about 10 filters in it - all are combo boxes for selecting values from tables with FK from products table.

running the query over all the products takes a long time, since I need to calculate and show per each result its total purchases.

What is the most efficient approach for implementing the search?

One approach is to put inside the select query of the result criterias to match values from the form for example:

select productType from products where productType= nz([forms]![searchProduct]![productTypeSelect],[productType])

in this way the query is calculated for each search which takes quite a long time.

Maybe the most efficent way is to build a table with all the products details first on each page load, and then to search in it only?

In this solution- I guess it would be best to save the last time that this calculated table was created and to only re-populate it if it wasn't updated recently. If so, how do I prevent other users from search the table while its being re-populated?

Please don't post duplicate threads... that creates much more confusion than the alternative (a thread being moved if needed). Your thread in Office Dev has been deleted.


The Where clause you have got is the kind of thing you should be using - but it should be applied to your main query, rather than as a separate query.

It should run quickly, because the check for the form value (and nz) should only run once for the whole query. If the form value is Null the rest of the condition should actually be ignored (because it will be productType=productType ), otherwise it will be treated as a direct comparison between the field and the form value for each row.

Quote:

---

running the query over all the products takes a long time, since I need to calculate and show per each result its total purchases.

---

I'm not sure what you meant by that part.. can you clarify?