Bernard,
If you don't have an index field FoxPro has to read the entire table to find the records that meet the criteria.
the whole table is slogged across your network If you have a index on the field, FoxPro only reads the much smaller parts of the CDX file to know which records need to be actually read from the table.
If you want fast adhoc queries build a reasonable set of indexes on the fields and if you let them pick non-indexed fields they just have to live with slower performance.
I'd just build an index on the date field and change the query to selecting dates between 1/1/year and 12/31/year, because a simple index would work just as well for daily, weekly, quarterly and annual requests.
>>Bernard,
>>
>>Have you tried creating an index tag on the inv_date field? Make sure the index expression is the same as the expression in your WHERE clause e.g. INDEX ON YEAR(inv_date). Otherwise your query will not be optimized.
>>
>>George
>I know about index's george but sometimes the queries can be adhoc. What I described was just an example to show that the query is simple and no join is involved. It is not possible to create an index for every query that I may need!