Skip to Content
0
Former Member
May 30, 2008 at 04:03 PM

Cross table with null values

30 Views

Post Author: YannG

CA Forum: General

Hello all,

I've 3 tables I'm trying to resume in a crosstable. I've created groups by SalesRep, Customer, CodeUnit. For each unit, I want a tab which includes a column for each of the past 6 years and a lign for each month. Problem is, no sales were recorded in March for the past 6 years so the line March is missing. Same thing happen with the year's column; if a product wasn't distributed in 2004, I will get a cross table with 2003 - 2005 - 2006 - 2007 - 2008 which is not what I want. I also want to avoid duplicating data with double SELECT formulas as I'm dealing with 7 million records.

I read a few posts in this forum in regards to Null values with the Isnull function. However, I cannot figure out where to apply this function.

Tables:Sales : NumberMonth, NumberYear, SoldUnits, SalesRep, Customer, CodeUnit...Month: DescMonth, NumberMonth...Unit: CodeUnit, Description...

Tables are linked as follow (inner join) : Unit.Codeunit = Sales.CodeUnit AND Month.NumberMonth = Sales.Numbermonth

Any help will be of course appreciated.

Cheers,Yann