Column total in dynamic report.

[JEA]

I'm struggling to find a way of creating a column total in a dynamic report. My db is attached below. If you open the form [ServiceSchedDateCollector], enter 01/11/07 and click the button it will show you the report I'm talking about ([Service Schedule Report]). I would like to have a Sum for each date.

The problem is the data is calculated, not straight out of a table. I've read Access help and my data isn't calculated as simply as they show in the help file and I can't think of a way of applying their example to my report.

I built the report in the wizard, not through code. If you could give the solution using this method I would appreciate it.

 

[JEA]

OK, think I can now give a better description of the problem.

The Report is in the format:

[StaffPIN]...|.......................[Day1]..............|.......................[Day2]..............|.......................[Day3]..............|
..123.......|..=workedHours([Day1],[SatffPIN]).|..=workedHours([Day2,[SatffPIN]).|..=workedHours([Day3],[SatffPIN]).|
..456.......|..=workedHours([Day1],[SatffPIN]).|..=workedHours([Day2,[SatffPIN]).|..=workedHours([Day3],[SatffPIN]).|

I want a total at the bottom of each collum. In the Report footer, I've put a TextBox with
=Sum(workedHours([Day1], [StaffPIN])

Not suprisingly, it doesn't work. How do I tell it to perform hoursWorked() for each [StaffPIN] and [Day1], and then Sum the result. The number of rows isn't fixed.

Hope that makes sense. Do you need anymore info?

 

[JEA]

Ok, trying to use this in a textbox:

=SELECT Sum(workedHours( [Day1] , [StaffPIN] )) AS [Total Worked]
FROM [Service Schedule Query_Crosstab]

in the hope of performing workedHours() on each [StaffPIN] (row), for the collumn heading [Day1].

Cay anyone help?