Merging a series of queries into one

[capax.solutions]

I have a table called "task_list" [taskID(str255) start_date(date)
due_date(date)] which contains lists of job types requiring attention.
I'd like to create a continuous form based on this information that
would show all the unique tasks, how many are not yet due to be
started, how many are currently active and how many are overdue. The
problem is that I can't find I single query to base the form on.

Is it possible to merge a series of queries like this:

select distinct(taskID) from task_list
for each taskID
{
select count(ID) as future_tasks from task_list where now() <
start_date
select count(ID) as current_tasks from task_list where now() >
start_date and now() < due_date
select count(ID) as overdue_tasks from task_list where now() >
due_date
}

Into one query that outputs something like this:

TaskID,future_tasks,current_tasks,overdue_tasks
EXAMPLE_JOB,6,2,1
TYPE2_EXAMPLE,0,3,0
FOO,12,0,0

Any help would be greatly appreciated.

 

[John Spencer]

Perhaps the following will work for you.

SELECT TaskID
, Count(IIF(Date()<Start_Date,1,Null)) as FutureTasks
, Count(IIF(Date()>Start_Date And Date()<Due_Date,1,Null)) as Current
, Count(IIF(Date()>Due_Date,1,Null)) as Overdue
FROM Task_List
GROUP BY TaskID
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[capax.solutions]

Ah now that's perfect, IIF eh, never used it before, this will come in
very handy.

Many thanks.