Quarterly attendance query help

[Danreed87]

Hi,

I have a table where I record attendance. I created a query to show how many times someone has attended in each quarter. I am wanting to be able to specify which year as atm it is showing all years. The SQL is below. From this I have a report where it displays the data and have conditional formatted it so that if they have not attended more than 4 times in one quarter the "cell" turns red - the only problem is is that some of the cells have no data in it. How can I get the query to display 0 if there is no data?

QUERY SQL:

TRANSFORM Sum(Abs([ATTENDANCE 2011].DRILL)) AS SumOfDRILL
SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL]
FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID = [ATTENDANCE 2011].ATTENDED.Value
GROUP BY [MEMBERS LIST].[NUMBER/NAME]
PIVOT "Qtr " & Format([DATE],"yyyy/q");

Cheers

Dan

[ADezii]

Modify the TRANSFORM Section of the SQL Statement to generate Zeros (0) where no Data exists, as in:

TRANSFORM IIf(Sum(Abs([ATTENDANCE 2011].DRILL)),Sum(Abs([ATTENDANCE 2011].DRILL)),0) AS SumOfDRILL
SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL]
FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID=[ATTENDANCE 2011].ATTENDED
GROUP BY [MEMBERS LIST].[NUMBER/NAME]
PIVOT "Qtr " & Format([DATE],"yyyy/q");