We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Access query to include lowest value from another table

ewarman
ewarman asked
on
Medium Priority
405 Views
Last Modified: 2012-05-06
Hi,
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?

Thanks.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.
Commented:
Hi,
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.