I have 2 tables in Access. One is a table of client data. The other is a list of tasks associated with the client. The table has the task and the date that the task must be completed by. They are linked by a clientID field.
I have a form with the client data. The users would like to see a field that shows the earliest date that a task associated with the client must be completed by. This would be known as the duedate for this client.
They must be able to sort and filter using this duedate field.
I am having some difficulty creating a query that would show this duedate field in a usable way.
I can use the dmin function to find the date. However when i do that queries on this field take forever and are not practical.
I have tried using a subquery to find the earliest date. However then the query cannot be updated and the users must be able to change information on the form.
Does anyone know of any other ways to do this?