Report totals from cross tab query

[Paul K]

I've created a query in Access 2007 that looks at people who have signed up for daily shifts in one of three positions; supervisor, member or trainee. I then created a cross tab query to sum the numbers by shift by position. From here I created a report that shows by shift the total number of people who signed up for a particular position each shift. I am able to create a total of the number of people who signed up with a simple

=sum[trainee]

in the footer. I am trying now to total the number of shifts for each of the positions where no one has signed up. I've tried

=count[trainee]>0,1

below the sum calculation in the footer but it does not work. I've tried adding

=iif[trainee]>0,1,0

as a new box in the detail and then totaling this new in the in the footer , but get the error message that it is too complex.

This seems like one of those thing that I should be able to do, but I just can't figure out how. As always thanks in advance for your help

[Rabbit]

None of your formulas should work. If you're calling a function, you need to surround the parameters with parentheses. Meaning that if you want to sum the column [trainee], then you need to use =sum([trainee])

[Paul K]

You are correct, it was my error in transcribing my question from my notes. The total formula that I use in the footer that works is

=sum([trainee])

. The formula in the footer that I get a message that it is typed incorrectly or is too complicated is

=count([trainee]>0)

. When I create a textbox called [TRN calc] in the detail using the control source formula of

=iif([trainee]=0,0,1)

and then try to sum this new textbox in the footer using

=sum([TRN Calc])

I get the message that it does not recognize " as a valid field name or expression. I would normally make some of these additional calculations in a query, but since I am using a crosstab query as my input for this report, I have not been able to find a way to do this through the query either.

[Rabbit]

If you mean to get a count of how many rows have a trainee field greater than 0, then you need to embed an iif function in a sum.

[jimatqsi]

Paul, what you've done with text box [TRN Calc] looks okay. I've made a similar example and got these results.
When my footer text box gets its value from =[TRN Calc] it displays the most recent value (last detail on the page) of the detail text box. When the footer text box gets its value from =Sum([TRN Calc]) I get the error you get.

Try this. Change the "Running Sum" property for your text box in the detail section. Leave the control source at =iif([trainee]=0,0,1 and that text box will begin to increment. You probably don't need that on the report, so make it invisible. Change your footer text box control source to =([TRN Calc])

That will give you what you want. Note that the Running Sum property can give you a total over a group or the whole report, as you choose.

[zmbd]

Paul K:
You might want to add Allen Browne's site to your bookmarks as there are several usefull tips and explanations on his site.
Related to your issue with CTQ his tips are here:Crosstab query techniques