MS Access / SQL help please?
April 21, 2009 7:03 AM Subscribe
I have a (hopefully easy) SQL question. I'm working in MS Access. Simplifying things a bit, I have a table with a TaskID, and a TaskStatusID. I want to get a list of each TaskID, and the newest TaskStatusID for each. (Details follow.)
The idea is a single task can have multiple statuses. My data looks like this:
TaskID = 1, TaskStatusID = 1
TaskID = 1, TaskStatusID = 2
TaskID = 1, TaskStatusID = 3
TaskID = 2, TaskStatusID = 4
TaskID = 2, TaskStatusID = 5
TaskID = 3, TaskStatusID = 6
TaskID = 4, TaskStatusID = 7
I want to get a list of each TaskID, and the newest TaskStatusID for each. So, I want a query that will return this:
TaskID = 1, TaskStatusID = 3
TaskID = 2, TaskStatusID = 5
TaskID = 3, TaskStatusID = 6
TaskID = 4, TaskStatusID = 7
The goal is to show that on a Continuous Form. I don't think this should be that difficult, but I'm learning as I go and just can't wrap my head around what the query should be. Any suggestions please? Thanks!
The idea is a single task can have multiple statuses. My data looks like this:
TaskID = 1, TaskStatusID = 1
TaskID = 1, TaskStatusID = 2
TaskID = 1, TaskStatusID = 3
TaskID = 2, TaskStatusID = 4
TaskID = 2, TaskStatusID = 5
TaskID = 3, TaskStatusID = 6
TaskID = 4, TaskStatusID = 7
I want to get a list of each TaskID, and the newest TaskStatusID for each. So, I want a query that will return this:
TaskID = 1, TaskStatusID = 3
TaskID = 2, TaskStatusID = 5
TaskID = 3, TaskStatusID = 6
TaskID = 4, TaskStatusID = 7
The goal is to show that on a Continuous Form. I don't think this should be that difficult, but I'm learning as I go and just can't wrap my head around what the query should be. Any suggestions please? Thanks!
Response by poster: This is assuming that a higher TaskStatusID is newer.
Valid assumption. I'll mess around with that, thanks.
posted by inigo2 at 7:17 AM on April 21, 2009
Valid assumption. I'll mess around with that, thanks.
posted by inigo2 at 7:17 AM on April 21, 2009
if newer taskstatusID is lower, all you have to do is substitute MIN(taskstatusID) for MAX....
Horselover's code is the way I would approach it, too.
posted by eaglehound at 7:52 AM on April 21, 2009
Horselover's code is the way I would approach it, too.
posted by eaglehound at 7:52 AM on April 21, 2009
The first question highlights a problem you have - equating 'newest' with 'highest'. Why not add a date field to the table and eliminate the guesswork?
The assumption will come back to bite you at some point.
posted by unixrat at 7:57 AM on April 21, 2009
The assumption will come back to bite you at some point.
posted by unixrat at 7:57 AM on April 21, 2009
This is assuming that a higher TaskStatusID is newer.
For argument's sake- what's the best practice for querying this if this assumption is not true, and there were a DateAdded field with a timestamp? I come across this problem occasionally, and wind up having to use subqueries that refer to values in the outer query, which seems hugely inefficient. Is there a better way to do this?
posted by mkultra at 8:01 AM on April 21, 2009
For argument's sake- what's the best practice for querying this if this assumption is not true, and there were a DateAdded field with a timestamp? I come across this problem occasionally, and wind up having to use subqueries that refer to values in the outer query, which seems hugely inefficient. Is there a better way to do this?
posted by mkultra at 8:01 AM on April 21, 2009
I would do:
posted by SNACKeR at 8:20 AM on April 21, 2009 [2 favorites]
select t.TaskID, t.TaskStatusID, t.DateAdded
from (
select TaskID, max(DateDadded) as DateDadded
from MyTable
group by TaskID
) tm
inner join MyTable t on tm.TaskID = t.TaskID and tm.DateAdded = t.DateAdded
posted by SNACKeR at 8:20 AM on April 21, 2009 [2 favorites]
Response by poster: Horselover Fat got me in the right direction, thanks everyone!
(And I've got a date in there, too, just trying to simplify things.)
posted by inigo2 at 8:25 AM on April 21, 2009
(And I've got a date in there, too, just trying to simplify things.)
posted by inigo2 at 8:25 AM on April 21, 2009
This thread is closed to new comments.
SELECT TaskID, MAX(TaskStatusID) FROM [TableName] GROUP BY TaskID
This is assuming that a higher TaskStatusID is newer.
posted by Horselover Fat at 7:16 AM on April 21, 2009