Calculating totals on a crosstab query

[Blair]

My sql
TRANSFORM Count(QDailyWheplingReport2.[NEST #]) AS [The Value]
SELECT QDailyWheplingReport2.[WHELPING DATE],
Count(QDailyWheplingReport2.[NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY QDailyWheplingReport2.[WHELPING DATE]
PIVOT QDailyWheplingReport2.[SHED #];

the data sheet view and my report looks like this

Shed # 1 2 3
Date Total
68 133 104 305
4/19/06 1 1
4/20/06 1 1
4/21/06 1 1
4/22/06 7 7
4/23/06 5 8 25 38
4/24/06 14 19 26 59
4/25/06 28 37 64 129

There are no totals at the bottom which is what I want
I would like a subtotal and gandtotal like this
SubTotal 47 64 125 236
Total 115 197 229 541
In my report I can get the total by summing the shed field,
but I can't figure out how to calculate the subtotal.
can you help?

I am trying to understand how the total works.
I have columns which are shed numbers from 1 to 50
The rows are dates, the days that litters were born.
The nests are the count of each litter, (the value)
At the top of the query data view is a total of the nests in each shed,
and as the dates or rows accumulate and liters are born each day, the
total goes down as the nest's column count goes up.

I would like a sum of all the days count, (Excluding the total at the top)
so I know how many litters were born in that shed.

My problem is when I make a report off this query and bring down the field
list
There is a [Whelping Date] field, a [Total Of NEST #] field, and all the
[Shed] fields
when I put the shed field in the report and view the report this total is at
the top and
all the days counts are there, BUT

How do I access the total to subtract it from my column sum, when I sum the
shed field?

Thanks for any suggestions
Blair

 

[Duane Hookom]

To get a "column" total/sum, add a text box to any group or report footer and
set its control source to:
=Sum([FieldName])
If that doesn't work then change the Value of your crosstab to:
TRANSFORM Val(Nz(Count(QDailyWheplingReport2.[NEST #]),0)) AS [The Value]