Calculating totals on a Crosstab Query

I made a report off this crosstab, I put an unbound text box in the page
footer and in the control field I put =Sum([1]), which gives me the grand
total of colum 1. I would like a sub total which does not include the 68
total at the top. It should be a total of 367 instead of 435 how can I
calculate this new total

Thanks Blair

mrBorn
1
2
3
4
5
6
7
8
9
11
32
33
34
35
51
53
55
58
59
Total Of NEST #


68
133
104
125
94
2
45
36
1
80
39
35
30
57
56
83
67
103
98
1256

5/11/2003



1















1

4/19/2006


1














1

2

4/20/2006


1
1









1
1

3

1
8

4/21/2006


1
2
4

3
1



1
2
2
2

4

2
24

4/22/2006


7
7
5

15
9

3
4
3
3
1
9
7
14
6
13
106

4/23/2006
5
8
25
22
36

20
10

4
7
7
10
4
8
7
17
15
27
232

4/24/2006
14
19
26
37
41

17
31
1
9
21
12
26
14
31
18
34
22
48
421

4/25/2006
28
37
64
50
67

36
33
1
19
27
21
41
25
46
26
51
38
76
686

4/26/2006
42
68
86
73
75

39
40

24
30
39
36
35
80
49
58
59
81
914

4/27/2006
41
88
82
90
72

55
46
4
22
31
28
42
26
92
72
63
63
74
991

4/28/2006
41
64
77
78
79

32
36

34
44
26
45
34
52
44
54
53
74
867

4/29/2006
60
61
94
83
59

20
26
1
14
30
25
37
24
47
58
46
36
37
758

4/30/2006
42
67
73
62
50

14
8
4
23
26
28
26
19
33
51
29
35
32
622

5/1/2006
23
43
51
41
41

10
8
4
13
9
16
13
21
22
26
16
29
20
406

5/2/2006
21
25
36
31
34

5
5

12
13
15
12
9
14
25
14
17
20
308

5/3/2006
14
22
22
16
17

3
4
3
2
9
6
7
10
10
15
11
18
6
195

5/4/2006
12
20
18
21
32

3
2

5
9
3
2
8
6
17
12
8
8
186

5/5/2006
10
16
12
24
16

1
1

4
5
10
3
3
2
9
6
4
9
135

5/6/2006
4
12
5
11
11

1
2


1
1
2
1
3
5
5
4
2
70

5/7/2006
2
8
5
7
9

1
1
1
2
3

1

3
3
3
1

50

5/8/2006
2
4
5
6
8

2
2

1
6
6
4
3
1
3
2
5
1
61

5/9/2006
2
2
3
3
3





1

1

2
1
3
2

23

5/10/2006
1

3
10
1



1



1

1




18

5/11/2006
1
1
1

2












1
1
7

5/12/2006


1
1
3





1


2
1
2
1


12

5/13/2006
1
2
2

3










1


1
10

5/14/2006

2


1










1



4

5/15/2006
1
4












1
1



7

5/16/2006



2















2

5/17/2006



2
1













1
4

5/18/2006



1
1













1
3

5/20/2006


















1
1

5/22/2006


















1
1

Opps didn't post like I thought it would
sorry
"Blair" wrote in message
...
I made a report off this crosstab, I put an unbound text box in the page
footer and in the control field I put =Sum([1]), which gives me the
grand total of colum 1. I would like a sub total which does not include the
68 total at the top. It should be a total of 367 instead of 435 how can I
calculate this new total

Thanks Blair

mrBorn
1
2
3
4
5
6
7
8
9
11
32
33
34
35
51
53
55
58
59
Total Of NEST #


68
133
104
125
94
2
45
36
1
80
39
35
30
57
56
83
67
103
98
1256

5/11/2003



1















1

4/19/2006


1














1

2

4/20/2006


1
1









1
1

3

1
8

4/21/2006


1
2
4

3
1



1
2
2
2

4

2
24

4/22/2006


7
7
5

15
9

3
4
3
3
1
9
7
14
6
13
106

4/23/2006
5
8
25
22
36

20
10

4
7
7
10
4
8
7
17
15
27
232

4/24/2006
14
19
26
37
41

17
31
1
9
21
12
26
14
31
18
34
22
48
421

4/25/2006
28
37
64
50
67

36
33
1
19
27
21
41
25
46
26
51
38
76
686

4/26/2006
42
68
86
73
75

39
40

24
30
39
36
35
80
49
58
59
81
914

4/27/2006
41
88
82
90
72

55
46
4
22
31
28
42
26
92
72
63
63
74
991

4/28/2006
41
64
77
78
79

32
36

34
44
26
45
34
52
44
54
53
74
867

4/29/2006
60
61
94
83
59

20
26
1
14
30
25
37
24
47
58
46
36
37
758

4/30/2006
42
67
73
62
50

14
8
4
23
26
28
26
19
33
51
29
35
32
622

5/1/2006
23
43
51
41
41

10
8
4
13
9
16
13
21
22
26
16
29
20
406

5/2/2006
21
25
36
31
34

5
5

12
13
15
12
9
14
25
14
17
20
308

5/3/2006
14
22
22
16
17

3
4
3
2
9
6
7
10
10
15
11
18
6
195

5/4/2006
12
20
18
21
32

3
2

5
9
3
2
8
6
17
12
8
8
186

5/5/2006
10
16
12
24
16

1
1

4
5
10
3
3
2
9
6
4
9
135

5/6/2006
4
12
5
11
11

1
2


1
1
2
1
3
5
5
4
2
70

5/7/2006
2
8
5
7
9

1
1
1
2
3

1

3
3
3
1

50

5/8/2006
2
4
5
6
8

2
2

1
6
6
4
3
1
3
2
5
1
61

5/9/2006
2
2
3
3
3





1

1

2
1
3
2

23

5/10/2006
1

3
10
1



1



1

1




18

5/11/2006
1
1
1

2












1
1
7

5/12/2006


1
1
3





1


2
1
2
1


12

5/13/2006
1
2
2

3










1


1
10

5/14/2006

2


1










1



4

5/15/2006
1
4












1
1



7

5/16/2006



2















2

5/17/2006



2
1













1
4

5/18/2006



1
1













1
3

5/20/2006


















1
1

5/22/2006


















1
1

Column
1

68

5
14
28
42
41
60
42
23
21
26
14
7
3

Total 367 (This Total is the one I am trying to calculate)
Grand Total 435

Thanks Blair

"Blair" wrote in message
...
I made a report off this crosstab, I put an unbound text box in the page
footer and in the control field I put =Sum([1]), which gives me the
grand total of colum 1. I would like a sub total which does not include the
68 total at the top. It should be a total of 367 instead of 435 how can I
calculate this new total

Thanks Blair

mrBorn
1
2
3
4
5
6
7
8
9
11
32
33
34
35
51
53
55
58
59
Total Of NEST #


68
133
104
125
94
2
45
36
1
80
39
35
30
57
56
83
67
103
98
1256

5/11/2003



1















1

4/19/2006


1














1

2

4/20/2006


1
1









1
1

3

1
8

4/21/2006


1
2
4

3
1



1
2
2
2

4

2
24

4/22/2006


7
7
5

15
9

3
4
3
3
1
9
7
14
6
13
106

4/23/2006
5
8
25
22
36

20
10

4
7
7
10
4
8
7
17
15
27
232

4/24/2006
14
19
26
37
41

17
31
1
9
21
12
26
14
31
18
34
22
48
421

4/25/2006
28
37
64
50
67

36
33
1
19
27
21
41
25
46
26
51
38
76
686

4/26/2006
42
68
86
73
75

39
40

24
30
39
36
35
80
49
58
59
81
914

4/27/2006
41
88
82
90
72

55
46
4
22
31
28
42
26
92
72
63
63
74
991

4/28/2006
41
64
77
78
79

32
36

34
44
26
45
34
52
44
54
53
74
867

4/29/2006
60
61
94
83
59

20
26
1
14
30
25
37
24
47
58
46
36
37
758

4/30/2006
42
67
73
62
50

14
8
4
23
26
28
26
19
33
51
29
35
32
622

5/1/2006
23
43
51
41
41

10
8
4
13
9
16
13
21
22
26
16
29
20
406

5/2/2006
21
25
36
31
34

5
5

12
13
15
12
9
14
25
14
17
20
308

5/3/2006
14
22
22
16
17

3
4
3
2
9
6
7
10
10
15
11
18
6
195

5/4/2006
12
20
18
21
32

3
2

5
9
3
2
8
6
17
12
8
8
186

5/5/2006
10
16
12
24
16

1
1

4
5
10
3
3
2
9
6
4
9
135

5/6/2006
4
12
5
11
11

1
2


1
1
2
1
3
5
5
4
2
70

5/7/2006
2
8
5
7
9

1
1
1
2
3

1

3
3
3
1

50

5/8/2006
2
4
5
6
8

2
2

1
6
6
4
3
1
3
2
5
1
61

5/9/2006
2
2
3
3
3





1

1

2
1
3
2

23

5/10/2006
1

3
10
1



1



1

1




18

5/11/2006
1
1
1

2












1
1
7

5/12/2006


1
1
3





1


2
1
2
1


12

5/13/2006
1
2
2

3










1


1
10

5/14/2006

2


1










1



4

5/15/2006
1
4












1
1



7

5/16/2006



2















2

5/17/2006



2
1













1
4

5/18/2006



1
1













1
3

5/20/2006


















1
1

5/22/2006


















1
1

Blair wrote:

I made a report off this crosstab, I put an unbound text box in the page
footer and in the control field I put =Sum([1]), which gives me the grand
total of colum 1. I would like a sub total which does not include the 68
total at the top. It should be a total of 367 instead of 435 how can I
calculate this new total


I think you need to to start over with a more detailed
explanation and a short example that clearly demonstrates
the effect you are trying to achieve.

--
Marsh
MVP [MS Access]

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

"Marshall Barton" wrote in message
...
Blair wrote:

I made a report off this crosstab, I put an unbound text box in the page
footer and in the control field I put =Sum([1]), which gives me the
grand
total of colum 1. I would like a sub total which does not include the 68
total at the top. It should be a total of 367 instead of 435 how can I
calculate this new total


I think you need to to start over with a more detailed
explanation and a short example that clearly demonstrates
the effect you are trying to achieve.

--
Marsh
MVP [MS Access]

Blair wrote:

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?


Are the strange numbers at the top returned in the query?
It looks like they are, probably with a Null in the
[Whelping Date] field. Run the query all by itself and
check if that's where that odd row is coming from.

If that row is in the query's result and the date is Null,
then you can get your subtotal by using a text box
expression like:
=Sum(IIf([Whelping Date] Is Not Null, [1], 0))

If that row is not coming from the query, then I don't have
the faintest clue what you are doing in the report nor how
you are calculating the total.

--
Marsh
MVP [MS Access]

Just got your reply, Thanks worked great
Blair
Now another Question on this type of query
depending on what sheds we use on the farm, the shed numbers are not always
the same.As the shed numbers change the results of the query and the data
sheet view will change accordingly. BUT my report made off this query will
not change automatically, I will have to manually add or delete the shed
fields in the report.
Is it possible to make a report that will display the appropriate results
according to the sheds that have data to display?
The data sheet view of the query would be great if some how the subtotal
and grandtotal rows I want to display could be written in the sql of the
crosstab query to display in the data sheet view. Is this possible?
"Marshall Barton" wrote in message
...
Blair wrote:

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?


Are the strange numbers at the top returned in the query?
It looks like they are, probably with a Null in the
[Whelping Date] field. Run the query all by itself and
check if that's where that odd row is coming from.

If that row is in the query's result and the date is Null,
then you can get your subtotal by using a text box
expression like:
=Sum(IIf([Whelping Date] Is Not Null, [1], 0))

If that row is not coming from the query, then I don't have
the faintest clue what you are doing in the report nor how
you are calculating the total.

--
Marsh
MVP [MS Access]

I spoke too soon Marshall, It worked for shed 1 or column 1, but I need a
text box for each column so how do I tie this IIf statment to each shed # or
column
Thanks Blair
"Marshall Barton" wrote in message
...
Blair wrote:

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?


Are the strange numbers at the top returned in the query?
It looks like they are, probably with a Null in the
[Whelping Date] field. Run the query all by itself and
check if that's where that odd row is coming from.

If that row is in the query's result and the date is Null,
then you can get your subtotal by using a text box
expression like:
=Sum(IIf([Whelping Date] Is Not Null, [1], 0))

If that row is not coming from the query, then I don't have
the faintest clue what you are doing in the report nor how
you are calculating the total.

--
Marsh
MVP [MS Access]

Man, this just keeps getting better and better ;-)

To do those other calculations over varying shed situations,
I think you might need something more like this:

TRANSFORM Count(.[NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]
UNION ALL
TRANSFORM Sum(IIf(([Whelping Date] Is Not Null,[NEST #],0))
AS [The Value]
SELECT Null,
Sum(IIf([Whelping Date] Is Not Null,[NEST #],0))
AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]
UNION ALL
TRANSFORM Sum([NEST #]) AS [The Value]
SELECT Null,
Sum([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]

I think that may be sufficient in datasheet view.

To bind that to a report when you have no idea what the shef
numbers might be is a whole 'nother problem. If you want to
pursue it, see if you can get some ideas from the Crosstab
demo database at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane
--
Marsh
MVP [MS Access]


Blair wrote:
Now another Question on this type of query
depending on what sheds we use on the farm, the shed numbers are not always
the same.As the shed numbers change the results of the query and the data
sheet view will change accordingly. BUT my report made off this query will
not change automatically, I will have to manually add or delete the shed
fields in the report.
Is it possible to make a report that will display the appropriate results
according to the sheds that have data to display?
The data sheet view of the query would be great if some how the subtotal
and grandtotal rows I want to display could be written in the sql of the
crosstab query to display in the data sheet view. Is this possible?

Blair wrote:
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?

"Marshall Barton" wrote
Are the strange numbers at the top returned in the query?
It looks like they are, probably with a Null in the
[Whelping Date] field. Run the query all by itself and
check if that's where that odd row is coming from.

If that row is in the query's result and the date is Null,
then you can get your subtotal by using a text box
expression like:
=Sum(IIf([Whelping Date] Is Not Null, [1], 0))

If that row is not coming from the query, then I don't have
the faintest clue what you are doing in the report nor how
you are calculating the total.

I get a syntax error (missing operator) in query expression 'count(.[Nest
#])'.
I removed the dot in front of [Nest #]

Then I get another syntax error (missing operator) in query '[Shed #]
UNION ALL
TRANSFORM Sum(IIf(([Whelping Date] Is Not Null,[NEST #],0))
AS [The Value]
SELECT Null,
Sum(IIf([Whelping Date] Is Not Null,[NEST #],0))
AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]
UNION ALL
T'.
I'm not sure what to look for
Thanks Blair

TRANSFORM Count(.[NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]
UNION ALL
TRANSFORM Sum(IIf(([Whelping Date] Is Not Null,[NEST #],0))
AS [The Value]
SELECT Null,
Sum(IIf([Whelping Date] Is Not Null,[NEST #],0))
AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]
UNION ALL
TRANSFORM Sum([NEST #]) AS [The Value]
SELECT Null,
Sum([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]

"Marshall Barton" wrote in message
...
Man, this just keeps getting better and better ;-)

To do those other calculations over varying shed situations,
I think you might need something more like this:

TRANSFORM Count(.[NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]
UNION ALL
TRANSFORM Sum(IIf(([Whelping Date] Is Not Null,[NEST #],0))
AS [The Value]
SELECT Null,
Sum(IIf([Whelping Date] Is Not Null,[NEST #],0))
AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]
UNION ALL
TRANSFORM Sum([NEST #]) AS [The Value]
SELECT Null,
Sum([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]

I think that may be sufficient in datasheet view.

To bind that to a report when you have no idea what the shef
numbers might be is a whole 'nother problem. If you want to
pursue it, see if you can get some ideas from the Crosstab
demo database at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane
--
Marsh
MVP [MS Access]


Blair wrote:
Now another Question on this type of query
depending on what sheds we use on the farm, the shed numbers are not
always
the same.As the shed numbers change the results of the query and the data
sheet view will change accordingly. BUT my report made off this query will
not change automatically, I will have to manually add or delete the shed
fields in the report.
Is it possible to make a report that will display the appropriate
results
according to the sheds that have data to display?
The data sheet view of the query would be great if some how the subtotal
and grandtotal rows I want to display could be written in the sql of the
crosstab query to display in the data sheet view. Is this possible?

Blair wrote:
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?

"Marshall Barton" wrote
Are the strange numbers at the top returned in the query?
It looks like they are, probably with a Null in the
[Whelping Date] field. Run the query all by itself and
check if that's where that odd row is coming from.

If that row is in the query's result and the date is Null,
then you can get your subtotal by using a text box
expression like:
=Sum(IIf([Whelping Date] Is Not Null, [1], 0))

If that row is not coming from the query, then I don't have
the faintest clue what you are doing in the report nor how
you are calculating the total.