This is on the back of a Previous post. Quite simple but not sure how to proceed.

This Crosstab is showing totals for all the advisors but if an advisor hasn't make a particular sale, it doesn't show here. How can i get it to show, even if the value is 0?

============================

SQL Behind this crosstab (SalesAllQ is a Union Query combining my two tables)

TRANSFORM Sum(Nz([GrossWrittenAMT],0)) AS WrittenAMT
SELECT SalesALLQ.Status, Sum(SalesALLQ.GrossWrittenAMT) AS [zALL CIMS]
FROM SalesALLQ LEFT JOIN StaffAdvisorsAllQ ON SalesALLQ.AdvisorID = StaffAdvisorsAllQ.STAFFID
WHERE (((SalesALLQ.GrossWrittenDate)>=DateSerial(Year(Date()),Month(Date()),1) And (SalesALLQ.GrossWrittenDate)<=Now()) AND ((SalesALLQ.Status)="PIPELINE"))
GROUP BY SalesALLQ.Status
ORDER BY StaffAdvisorsAllQ.STAFFNAME
PIVOT StaffAdvisorsAllQ.STAFFNAME;