Results 1 to 1 of 1
2001-04-10, 04:23 #1
- Join Date
- Apr 2001
- Melbourne, Australia
- Thanked 1 Time in 1 Post
Sorting tables with lookup fields
I am using A97 to create an application which will involve a primary table of over 400,000 records, in which several fields are resolved through a variety of secondary lookup tables. Essentially, it is a browser application, and I would like to be able to read these primary tables from a CD.
In order to perform a variety of sorts on various fields or combinations of fields, I have created a number of indexes. Then, if I open the table directly, using the sort functions is extremely fast, as expected.
Next I created a "continuous forms" form which access this data, and used the various lookup tables to resolve data in the relevant fields. I would like to provide a "sort" button to allow the data to be sorted according to the various indexes I have created.
If I do not use any joined tables, Access seems to use the indexes, but if I base the form on a query which joins the lookup tables, the indexes do not seem to be available or used when I change the sort order, and so of course performance suffers. I have tried to resolve this by basing the form on the 'raw' table, and deferring resolution of the lookups to the form itself. However, this approach results in a delay in repainting the form as the lookups are resolved. When the source data is on a CD, the lookups seem to fail altogether.
I would be grateful for any suggestions about how to provide fast sorting for queries based on large tables like this.