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?