• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Access query to include lowest value from another table

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?

1 Solution
To achieve all that you want you would need to create a separate table of min dates using a make-table query and then join that table to the main table to provide the recordsource for your form.
If you try to use a query that in any way involves an aggregate function you will produce a non-updateable result.
And to provide a 'sort' capability the field has to be part of the recordssource.
ewarmanAuthor Commented:
I have been playing around with this for a while. Making a new table did not seem practical as data could change at any time and then I would have to recreate the table. Unless there is another way to do this.
I think I will try using the Dmin function as I had before. However when a user runs a search I will use a subquery that will look up the primary key in a separate query that just has the minimum values.
I think I will try this a little bit and see if I can get it working this way.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now