Crosstab Query - Null Values

Question

The data resides in a single table.  I have a totals query (qryYTDFullCommissionTotal) to calculate the year-to-date total of the FullCommission field by Year and Month. 

SELECT Format([RevenueDate],"yyyy") AS Yr, Format([RevenueDate],"mmm") AS Mo, Format(DSum("FullCommission","Revenue","DatePart('m', [RevenueDate])<=" & [MoNN] & " And DatePart('yyyy', [RevenueDate])=" & [Yr] & " AND [SaleStatusID] <> 3"),"#,##0") AS YTDFullCommission, Format([RevenueDate],"mm") AS MoNN

FROM Revenue

GROUP BY Format([RevenueDate],"yyyy"), Format([RevenueDate],"mmm"), Format([RevenueDate],"mm")

ORDER BY Format([RevenueDate],"yyyy"), Format([RevenueDate],"mm");

I have a crosstab query (qryYTDFullCommissionXTab) which uses the totals query to report YTD amounts with months across the top and years down the side.

TRANSFORM Sum([qryYTDFullCommissionTotal].YTDFullCommission) AS SumOfYTDFullCommission

SELECT [qryYTDFullCommissionTotal].Yr

FROM qryYTDFullCommissionTotal

GROUP BY [qryYTDFullCommissionTotal].Yr

PIVOT [qryYTDFullCommissionTotal].Mo In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

For months without a revenue record, null is shown as the year-to-date value in the crosstab query.  Is it possible to calculate the year-to-date value for those months which show as null?

Answer 1

Thanks so much for your response.  It gave me the idea to set up a FirstOfMonth table – one date for each month.  As you suggested, an outer join from this table to the transaction table gave me at least one record for every month, which I then converted to zero with the Nz function.

Answer 2

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file includes an example of the use of the NZ() function in a query to return a zero in place of a NULL where values for each month are returned, regardless of there having been any transactions in the month, by joining an auxiliary Calendar table to the Transactions table in a LEFT OUTER JOIN.  To return the end of month balance over the year the query could easily be extended by the inclusion of a correlated subquery:

SELECT YEAR(calDate) AS calYear, FORMAT(calDate,"mmmm") AS calMonthName,

VAL(NZ(SUM(TransactionAmount),0)) AS TotalAmount,

   (SELECT SUM(NZ(TransactionAmount))

FROM Transactions

WHERE YEAR(TransactionDate) <= YEAR(Calendar.calDate)

AND MONTH(TransactionDate) <= MONTH(Calendar.calDate)

AND TransactionDate BETWEEN #2009-01-01# AND #2009-12-31#) As Balance

FROM Calendar LEFT JOIN Transactions ON Calendar.calDate = Transactions.TransactionDate

WHERE calDate BETWEEN #2009-01-01# AND #2009-12-31#

GROUP BY YEAR(calDate), MONTH(caldate), FORMAT(calDate,"mmmm");

It would then be possible to use this query as the basis for a crosstab query.

Answer 3

If you want to display a zero in place of null, try using the Nz() function.