Selecting open tasks in a timetracker (1 Viewer)

[pranavnegandhi]

The timetracker that I am building stores the history of the task in multiple rows. Here's a simplified view.

Task ID | Assignor ID | Assignee ID | Description | Status
1 | 5 | 10 | I could not make this change. Please try at your end | assigned
1 | 10 | 2 | I made this change. Please check it. | resolved
1 | 2 | 10 | Please make a few modifications. | assigned
1 | 10 | 2 | Done. | resolved
1 | 10 | 10 | Cool. You're in for a raise! | closed

If i want to select task histories of tasks which are not closed, i.e. the status in the last record is not set to closed, how would i go about doing it? As of now I am doing this through code, where I cycle through the recordset and skip displaying the rows if the task has been closed. But I'm sure there's an easier way of doing this.

Any help will be appreciated.

 

[Dennisk]

use a query to retreive only those records where status <> closed

an example
SELECT * FROM myTable WHERE [Status] <> 5
I am assuming closed = 5

 

[pranavnegandhi]

Thank you for your reply.

That's not quite what I want though. This is a table of the history of changes made to a particular item. So the latest record in the table would indicate the current status, and I want the history of all items for which the current status is not closed.

Table - Task
Task ID | Name
1 | Upload images
2 | Feedback form
3 | Add new database table

Table - TaskHistory
Task ID | Assignor ID | Assignee ID | Description | Status
1 | 5 | 10 | I could not make this change. Please try at your end | assigned
1 | 10 | 2 | I made this change. Please check it. | resolved
1 | 2 | 10 | Please make a few modifications. | assigned
1 | 10 | 2 | Done. | resolved
1 | 2 | 2 | Cool. You're in for a raise! | closed

I hope this explains it better.

 

[pranavnegandhi]

To put the above in words, I want to select all records in TaskHistory where the last record for a given task ID is not closed.

 

[Dennisk]

I'm still not quite sure what you require but this query select the last record for each Task and only return those that are not closed

SELECT tblTaskHistory.TaskID, Last(tblTaskHistory.Status) AS LastOfStatus1
FROM tblTaskHistory
WHERE (((tblTaskHistory.Status)<>"Closed"))
GROUP BY tblTaskHistory.TaskID;