Crosstab for specific number of days

[Molasses26]

Hi!
I have an corsstab query that counts the number of tasks created each day.
Currently I have it set to give me a daily count for the most current 7 days
which gives me 5 columns (workdays). However if we happen to have a holiday
in there then we only see 4 columns. I'm wondering if there is some way
(without getting too complex) to set it up so it ALWAYs shows 5 days worth of
data no matter how many "non-work" days there are.
Thanks!

Current SQL:
TRANSFORM Count(dbo_research_task.task_nbr) AS CountOftask_nbr
SELECT dbo_research_task.work_type, dbo_research_work_type.description
FROM dbo_research_task INNER JOIN dbo_research_work_type ON
dbo_research_task.work_type = dbo_research_work_type.work_type
WHERE (((DateValue([initiated]))>DateAdd("d",-7,Now())) AND
((dbo_research_task.requested_by)="AUTO"))
GROUP BY dbo_research_task.work_type, dbo_research_work_type.description
PIVOT DateValue([initiated]);

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing number from 0
(zero) through 100. Then try this query --
TRANSFORM
Sum(IIf(DateAdd("d",[CountNUM],DateAdd("d",-6,Date()))=[initiated],1,0)) AS
Expr2
SELECT dbo_research_task.work_type, dbo_research_work_type.description
FROM CountNumber, dbo_research_task INNER JOIN dbo_research_work_type ON
dbo_research_task.work_type = dbo_research_work_type.work_type
WHERE (((dbo_research_task.initiated) Between DateAdd("d",-6,Date()) And
Date()) AND ((dbo_research_task.requested_by)="AUTO") AND
((CountNumber.CountNUM)<=6))
GROUP BY dbo_research_task.work_type, dbo_research_work_type.description
PIVOT DateAdd("d",[CountNUM],DateAdd("d",-6,Date()));