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:
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: