Retrieve next task only (1 Viewer)

galinda13

New member
Local time
Today, 06:33
Joined
Apr 8, 2013
Messages
5
Hello there,
I've been scratching my head with this for a few hours now and I'm sure I'm missing something obvious!

I have two tables - one is PROJECTS and the other is TASKS.

PROJECTS contains a list of projects currently to be undertaken by the department.

TASKS contains a list of tasks within each project - so for each project, there are many tasks.

I want to have a query which retrieves a list of the projects, and ONLY the next task due on the project. The tasks all have a due date.

Initially I thought this would be easy, so I created a query with my TASKS in the order I wanted them in - i.e. all sorted by due date, and then their task ID in case there is more than one task due on one date. I then created a second query, which is just displaying the projects, and I have linked it to the field in TASKS which identifies which project the task is for.

I can't work out how to only display the top record per project - as I have set up a one-to-many relationship, Access gives me a list of every project and every task, with the project details repeated each time.

I have experimented with changing 'Top Values' in my Tasks query to 1, but this of course only brings back 1 task, rather than the topmost task per query.

I hope I have given enough information to be useful, I'm sure this is quite simple but I can't get my head around it! :banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:33
Joined
May 7, 2009
Messages
19,169
How do you determine which task is next? Do you have another field to tag if the task has been done, what is the field name. Do you only need the next (1) task when another task has been x
complered?
 

galinda13

New member
Local time
Today, 06:33
Joined
Apr 8, 2013
Messages
5
Thanks for the reply! The tasks have the following fields:

TASK_ID - Autonumber unique to each task
TASK_PROJECT - Integer, number of the project this relates to
TASK_TYPE - Integer, type of task this is
TASK_NAME - Name of the task
TASK_STAGE - Integer, what stage of the project is this task within?
TASK_DUEDATE
TASK_COMPLETEDATE

Tasks are ordered by Stage Ascending, then Due Date Ascending. This means that the tasks in the earliest stages of the project come first, and are then ordered by their due date. They are also then sorted by Task ID ascending, only because if there are 2 tasks due on the same day, I want the task input onto the database first to show first.

The TASK_COMPLETEDDATE field is null if the task has not been completed, so we can find uncompleted task by searching this field on null.

I only want the very next task to appear per project. The reason for this is that I want to have a form with a full list of projects, and just the next thing that has to happen in that individual task for the project to move forward.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:33
Joined
May 7, 2009
Messages
19,169
you may try this query, but haven't
really tested it.
Replace [Project ID] with correct field name.

SELECT PROJECTS.[Project ID], (SELECT TOP 1 TASKS.Task_Type FROM TASKS WHERE TASKS.Task_Project=PROJECTS.[Project ID] AND TASKS.Task_CompleteDate IS NULL ORDER BY TASKS.Task_Stage) As Task_Type,
(SELECT TOP 1 TASKS.Task_Name FROM TASKS WHERE TASKS.Task_Project=PROJECTS.[Project ID] AND TASKS.Task_CompleteDate IS NULL ORDER BY TASKS.Task_Stage) As Task_Name,
(SELECT TOP 1 TASKS.Task_Stage FROM TASKS WHERE TASKS.Task_Project=PROJECTS.[Project ID] AND TASKS.Task_CompleteDate IS NULL ORDER BY TASKS.Task_Stage) As Task_Stage,
(SELECT TOP 1 TASKS.Task_DueDate FROM TASKS WHERE TASKS.Task_Project=PROJECTS.[Project ID] AND TASKS.Task_CompleteDate IS NULL ORDER BY TASKS.Task_Stage) As Task_DueDate,
(SELECT TOP 1 TASKS.Task_CompleteDate FROM TASKS WHERE TASKS.Task_Project=PROJECTS.[Project ID] AND TASKS.Task_CompleteDate IS NULL ORDER BY TASKS.Task_Stage) As Task_CompleteDate
FROM PROJECTS ORDER BY PROJECTS.[Project ID];
 

galinda13

New member
Local time
Today, 06:33
Joined
Apr 8, 2013
Messages
5
That gave an error about only being able to return 1 record from a subset - however it got me started and I just set up a query showing only open tasks, and then drew the data from that.

Thanks for your help :)
 

Users who are viewing this thread

Top Bottom