Enumerating Months with No Data in Summary Query 1

[Quehay]

I'm trying to sort counts of Visit Dates by month, which works fine using GROUP BY DatePart("m",[VisitDate] along with COUNT ([VisitDate]). The problem is getting a row with zero value for those months with no data. Does anyone have a technique for enumerating the months (maybe an outer join with a table of months--group by each month?)

Thanks for any insight

 

[MichaelRed]

Quehay,

I generally use the outter join approach for these, however you can (at least with crosstab queries) include the months in the "Column Headings propert"



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Quehay]

Thanks! Yeah, as long as there's at least one value per month the crosstab wizard does it automatically.

 

[MichaelRed]

But there is a property in the CrossTab Query "Column Headings" ? Which will FORCE them.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Quehay]

Thanks again Michael! I figured that that's what you meant but I hadn't got around to trying it out. I was also going to try creating a subquery that substitutes the datepart "m" value for actual dates and then doing an outer join with a table that has values 1-12 in a Summary query with COUNT of Visits and GROUPBY MonthValue. Seems like this is a view of info that would be useful for a lot of situations. Maybe an FAQ is in order. . .

 

[MichaelRed]

Actually, I'm not a fan of this approach, as it obscures the function/process for many. Some variation on the last part is - IMHO - much better, as it has MUCH more flexibility, and is easier for all to 'see' whats going on 'behind' the process. Many of the problems I deal with are caused by someone being 'Cute' or 'Efficient' - and obscuring the real process.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over