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