Unanswered: Time Series in Pivot Chart... creating the gaps
I'm exasperated with this issue and dearly need some help. I work with a team of epidemiologists who create these epicurve charts using Access pivot charts. However, try as I might, I cannot get these pivotcharts to display the category axis in a time series. The sql statements I use only return an aggregate of disease cases for the weeks that they occur so if there is a week without a disease incident then it won't get displayed. For common diseases like influenza, all weeks of the year get displayed - great! - but this isn't the case for the less common diseases and creates a false impression. I'd happily use a vanilla stacked bar chart except the epi's love doing their analysis with their pivotcharts.
Does anyone know how I can modify my sql to include zero cases for the missing weeks or somehow tweak the pivotchart to display a complete time series? Thank you.
If you create a table that only has dates in it, one date for each week, then you can reference this "time" table in your SQL to be sure that each week is included in your time line, even if there are no sick people that week.
Thanks GolferGuy. I did try creating a table with WeekID and ZeroCount fields. WeekID went from 1 to 53, ZeroCount was all zeroes. Joined it to my original query and created a calculated field that added the zero to the cases. Doing it this way seems to require the case year as well (the epi's like to see 5 years of disease peaks) so I created another field with the single year 2000 in tbl_WeekNO. Anyhow, I still couldn't get it to work, still missing weeks and not getting the zero value. The sql is below:
SELECT tbl_WeekNO.WeekID, [Cases]+[ZeroCount] AS Combined
FROM tbl_WeekNO LEFT JOIN qry_Weekly ON tbl_WeekNO.WeekID = qry_Weekly.Week
GROUP BY tbl_WeekNO.WeekID, [Cases]+[ZeroCount]
ORDER BY tbl_WeekNO.WeekID;
If there is another way that doesn't require me building 20 historic years worth of weeks (plus the future years!), something a bit more elegant then I'd go for that but if that is the only way to do it, I'll do it. If I can get it to work! Why did Microsoft build a pivotchart that won't do time series?
This needs to be from tbl_WeekNO because tbl_WeekNO is what is giving you the filler for the gaps. But it you are only accepting records where there is a year of 2000 in qry_Weekly, then you will be excluding all the records from tbl_WeekNO that you are really wanting in.
But the better way to do this would be to have Qry_Weekly have the year criteria in it, then you can just leave the year code out of tbl_WeekNO.
Thanks GolferGuy - spot on! Works a treat, first time. Wish I'd joined this forum ages ago instead of mucking around wishing something would fall into place. If you're hitting the greens this w/e GolferGuy, may all your shots fly true.
"What other people may find in poetry or art museums, I find in the flight of a good drive". ~Arnold Palmer