Cross Tab Report Column Totals

[Scott Viney]

Morning All,

With the help of ppl here to create a crosstab query below, and a lot of
messing about I have been able to create a semi functional timesheet for my
hours every week.

But how can I create totals for the hours for each day? IE a sum of the
column of hours for a certain date.

I have in the page footer of my report a text box with the control source as
=Sum([D0]) etc for each day. But I always get an error. Is it possible to
do what Iam trying to do? If so how do I do it?

PARAMETERS [Forms]![frmDate]![cboStartDate] DateTime;
TRANSFORM Sum(tblTimeSheet.BillableHours) AS SumOfBillableHours
SELECT tblProjects.ProjectName, tblProjects.ProjectNumber,
Sum(tblTimeSheet.BillableHours) AS [Total Of BillableHours]
FROM tblProjects INNER JOIN tblTimeSheet ON tblProjects.ProjectID =
tblTimeSheet.ProjectID
WHERE (((tblTimeSheet.BillingDate) Between [Forms]![frmDate]![cboStartDate]
And ([Forms]![frmDate]![cboStartDate]+6)))
GROUP BY tblProjects.ProjectName, tblProjects.ProjectNumber
PIVOT "D" &
DateDiff("D",[Forms]![frmDate]![cboStartDate],tblTimeSheet.BillingDate)
IN ("D0","D1","D2","D3","D4","D5","D6");

Thanks for any help,
Scott

 

[Duane Hookom]

Don't attempt to sum in a page footer. Move your sums to a group footer or
report footer.

 

[Ken Sheridan]

You can't use an aggregate function in the ControlSource of a control in a
Page Footer, only Group or Report footers. You can, however, put a hidden
control in a Group or Report Footer, then refer to it as the ControlSource of
a text box in a Page Footer, e.g.

=[txtSumOfD0]

If you want page totals in a multi-page report you can put an unbound text
box in the page Footer and initialize it to zero in the Page Header's Print
event procedure:

Me.txtPageTotal = 0

then increment it in the Detail Section's Print event procedure with the
value of the relevant control:

If PrintCount = 0 Then
Me.txtPageTotal = Me.txtPageTotal + Me.Amount
End If

Examining the PrintCount property avoids any inadvertent double counting,
which can happen if the event fires more than once, as it can do in some
circumstances.