Access query to include lowest value from another table

Posted on 2009-02-10
Last Modified: 2012-05-06
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?

Question by:ewarman
    LVL 77

    Expert Comment

    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.

    Accepted Solution

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now