G
Guest
Hi,
We have three client categories for our top clients: A, B, and C. About
half of our clients do not have a category and are simply null. They are in a
field called ClientType. I am trying to do a report on item sales (field
name FullName) by ClientType. I am also trying to list year to date sales
and month to date sales. To do so, I am doing a left join on ClientType and
FullName. This will list the item number (FullName) followed by ClientType
and then show the quantity sold month to date and year to date and then the
amount in revenue month to date and year to date. The problem is that since
some of the client types have null values, they are not joining and are not
appearing in the month to date quantity and month to date amount values which
undercounts both these values. Is there a way to joing a null value with a
null value?
Thanks,
SELECT QTYCurrentYearByClientType1.Description,
QTYCurrentYearByClientType1.ClientType, QTYCurrentYearByClientType1.FullName,
QTYCurrentYearByClientType1.SalesDesc,
QTYCurrentMonthByClientType1.SumOfQuantity AS QTYMonth,
QTYCurrentYearByClientType1.SumOfQuantity AS QTYYear,
QTYCurrentMonthByClientType1.SumOfAmount AS AmountMonth,
QTYCurrentYearByClientType1.SumOfAmount AS AmountYTD,
QTYCurrentMonthByClientType1.SumOfTotalCost AS CostMonth,
QTYCurrentYearByClientType1.SumOfTotalCost AS CostYTD,
QTYCurrentMonthByClientType1.SumOfTotalMargin AS MarginMonth,
QTYCurrentYearByClientType1.SumOfTotalMargin AS MarginYTD
FROM QTYCurrentYearByClientType1 LEFT JOIN QTYCurrentMonthByClientType1 ON
(QTYCurrentYearByClientType1.ClientType =
QTYCurrentMonthByClientType1.ClientType) AND
(QTYCurrentYearByClientType1.FullName =
QTYCurrentMonthByClientType1.FullName);
We have three client categories for our top clients: A, B, and C. About
half of our clients do not have a category and are simply null. They are in a
field called ClientType. I am trying to do a report on item sales (field
name FullName) by ClientType. I am also trying to list year to date sales
and month to date sales. To do so, I am doing a left join on ClientType and
FullName. This will list the item number (FullName) followed by ClientType
and then show the quantity sold month to date and year to date and then the
amount in revenue month to date and year to date. The problem is that since
some of the client types have null values, they are not joining and are not
appearing in the month to date quantity and month to date amount values which
undercounts both these values. Is there a way to joing a null value with a
null value?
Thanks,
SELECT QTYCurrentYearByClientType1.Description,
QTYCurrentYearByClientType1.ClientType, QTYCurrentYearByClientType1.FullName,
QTYCurrentYearByClientType1.SalesDesc,
QTYCurrentMonthByClientType1.SumOfQuantity AS QTYMonth,
QTYCurrentYearByClientType1.SumOfQuantity AS QTYYear,
QTYCurrentMonthByClientType1.SumOfAmount AS AmountMonth,
QTYCurrentYearByClientType1.SumOfAmount AS AmountYTD,
QTYCurrentMonthByClientType1.SumOfTotalCost AS CostMonth,
QTYCurrentYearByClientType1.SumOfTotalCost AS CostYTD,
QTYCurrentMonthByClientType1.SumOfTotalMargin AS MarginMonth,
QTYCurrentYearByClientType1.SumOfTotalMargin AS MarginYTD
FROM QTYCurrentYearByClientType1 LEFT JOIN QTYCurrentMonthByClientType1 ON
(QTYCurrentYearByClientType1.ClientType =
QTYCurrentMonthByClientType1.ClientType) AND
(QTYCurrentYearByClientType1.FullName =
QTYCurrentMonthByClientType1.FullName);