MS Access / SQL help please?

April 21, 2009 7:03 AM

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!

posted by inigo2 to computers & internet (7 comments total)

You can start with:
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

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

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

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

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

I would do:

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 [2 favorites]

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