I've generated a report as per the attached image from this crosstab query:
TRANSFORM Count(tblSources.Source) AS CountOfSource
SELECT Year([DateTaken]) AS [Year]
FROM BiilsSourceOfLeads INNER JOIN tblSources ON BiilsSourceOfLeads.Source = tblSources.SourceID
GROUP BY Year([DateTaken])
ORDER BY Year([DateTaken]) DESC
I need to get a total at the end of each row to show the total number of leads for the year.
Is there a way to do this within the crossatb query? If not, how do I do it in the report?
I've tried adding a text box at the end and making its control source all of the columns added together like =[DVD]+[Magazine]+[Referra
l] etc.. but if any of the values in any row, I.E. any year, are null, then the calculation doesn't work, it just returns an empty field.