Filter query to show 1 "task" for each job

[tommy93]

Hello,



Currently i have a database that records jobs for a company. Each job has assigned "Tasks" (ie. call customer, call engineer) which are stored in another table for task history recording. These tasks have 3 fields; Task, Task Date and Task Time.

What I need is a way of building a query to only show the latest task for each job for their operations. At the moment, searching the tasks shows up all of the tasks for every job, but i need just the latest (or next) task for each job.
can anyone help?

Thanks

[June7]

Finding the most recent record is a common requirement.

Review this recent thread https://www.accessforums.net/queries...ble-39197.html on similar issue.

[tommy93]

Finding the most recent record is a common requirement.

Review this recent thread https://www.accessforums.net/queries...ble-39197.html on similar issue.

Hello,

After reading that it seems that sql only shows the last record. I need the last record for each job. For example;

These are the jobs. These are the job "tasks".

from that screen, i can see the next task for job 15 would to be to cancel an engineer on the 16th. (these dates are only dummy data)

Each job in the database can have several of these tasks assigned. Once one task is complete, a new one is tasked to the job. I need to run a query that will just show the latest task for each job.
The query at the moment returns all tasks, but i need it to filter to just show 1 for each job ID, which is the next job based on task date and time.

Thanks