I have the following situation
Table one, contains about 30 fields about people identity data, with an IDTag (text) as primary key
Table two, contains an custom numeric identifier of the area where people lives, having a code (text) as primary key, that is as well the foreign key in table one
Between the two tables is a one-to-one relation
The query I use is a select query that retrieves data from the two tables, based on various criteria filled in a form
The output is dispalyed in a subform of the main form
Now it starts the problem
A third table contains some history data of the persons in table one. The IDTag from table one is foreign key for table 3
For each IDTag we may have multiple records in table 3. Only some of the people (IDTag) in table one have corresponding record in table 3
I set a one to many relation between table 1 and 3, that shows all records in table one and only those from table 3 that matches a certain criteria provided by the form
It also shows only distinct values (some values may be doubled, so I need this)
The output will show all persons recorded on table one, with or without history, and fills a query field that shows if the person have a certain string in his history or N/A for those that does not.
If the first query works fine (about 1 min to process 100k records), when I add the relation with the third table, the query becomes incredibly sluggish, taking about 15 minutes to run.
The form is a dynamic one, requreying each time a field content is updated, but under these circumstances, becomes impossible to use.
Any ideea to speed up the stuff?