Calculating totals on a Crosstab Query

[Blair]

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]

Opps didn't post like I thought it would
sorry

 

[Blair]

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

 

[Marshall Barton]

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.

 

[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

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.

 

[Marshall Barton]

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.

 

[Blair]

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?

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.

 

[Blair]

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

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.

 

[Marshall Barton]

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/OtherLibraries.asp#Hookom,Duane
--
Marsh
MVP [MS Access]

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?

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.

 

[Blair]

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 #]

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/OtherLibraries.asp#Hookom,Duane
--
Marsh
MVP [MS Access]

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?

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.

 

[Marshall Barton]

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 #]

Man, did I make a mess of that or what. You think I can
blame it on all those square brackets you had to use because
of the funky characters in those field names? ;-)
Guess not. It's probably because my eyes aren't good
enought to see all those tiny little dots. I shouldn't feel
too bad though, you only found one of them ;-)

Tricky query, take two:

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 Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT Null,
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]
UNION ALL
TRANSFORM Count([NEST #])
SELECT Null, Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]

 

[Blair]

I want to apologize for the need for brackets.I hired a guy to do my db and
I thought he was a genius.Then he took off and I had to learn how to do
it.Then I found these newsgroups and have since then with yawls help found
out all the things he did wrong, like the need for all those brackets and
non normalization of the tables.
But I am going to try and get someone to redo my db, now I have an idea of
how it should be.

You have no idea how much I appreciate your help
Thanks Blair

I still get this error
syntax error (missing operator) in query '[Shed #]

UNION ALL
TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT Null,
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]
UNION ALL
TRANSFORM Count([NEST #])
SELECT Null, Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]

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 #]

Man, did I make a mess of that or what. You think I can
blame it on all those square brackets you had to use because
of the funky characters in those field names? ;-)
Guess not. It's probably because my eyes aren't good
enought to see all those tiny little dots. I shouldn't feel
too bad though, you only found one of them ;-)

Tricky query, take two:

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 Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT Null,
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]
UNION ALL
TRANSFORM Count([NEST #])
SELECT Null, Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY Null
PIVOT [SHED #]

 

[Marshall Barton]

I want to apologize for the need for brackets.I hired a guy to do my db and
I thought he was a genius.Then he took off and I had to learn how to do
it.Then I found these newsgroups and have since then with yawls help found
out all the things he did wrong, like the need for all those brackets and
non normalization of the tables.
But I am going to try and get someone to redo my db, now I have an idea of
how it should be.

You have no idea how much I appreciate your help

I still get this error
syntax error (missing operator) in query '[Shed #]

UNION ALL

. . .


I need a vacation!

I skipped the last step in my tests. It seems that you can
not use UNION with a crosstab query. To get around that, we
need to create the three cross tab queries as separate saved
queries.

Really tricky set of queries, Take 3:

qrySheds:
TRANSFORM Count([NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]

qrySubTotal:
TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

qryTotal:
TRANSFORM Count([NEST #])
SELECT "Total", Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY "Total"
PIVOT [SHED #]

Finally, the query that puts it all together:
SELECT * FROM qrySheds
UNION ALL
SELECT * FROM qrySubTotal
UNION ALL
SELECT * FROM qryTotal

 

[Blair]

you are getting closer qrysheds and qryTotal works
qrySubTotal has this error
Thanks Blair

syntax error (Comma) in query Expression Count(IIf(([Whelping Date] Is
Null,Null,[NEST #]))

TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

I want to apologize for the need for brackets.I hired a guy to do my db
and
I thought he was a genius.Then he took off and I had to learn how to do
it.Then I found these newsgroups and have since then with yawls help found
out all the things he did wrong, like the need for all those brackets and
non normalization of the tables.
But I am going to try and get someone to redo my db, now I have an idea
of
how it should be.

You have no idea how much I appreciate your help

I still get this error
syntax error (missing operator) in query '[Shed #]

UNION ALL

. . .


I need a vacation!

I skipped the last step in my tests. It seems that you can
not use UNION with a crosstab query. To get around that, we
need to create the three cross tab queries as separate saved
queries.

Really tricky set of queries, Take 3:

qrySheds:
TRANSFORM Count([NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]

qrySubTotal:
TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

qryTotal:
TRANSFORM Count([NEST #])
SELECT "Total", Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY "Total"
PIVOT [SHED #]

Finally, the query that puts it all together:
SELECT * FROM qrySheds
UNION ALL
SELECT * FROM qrySubTotal
UNION ALL
SELECT * FROM qryTotal

 

[Marshall Barton]

Arrggghhhh, Now I can't even copy and paste without blowing
it. There is an extra parenthesis in there.

Take ... I've lost count ;-):

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

Please, tell me that's a wrap ;-)
--
Marsh
MVP [MS Access]

you are getting closer qrysheds and qryTotal works
qrySubTotal has this error
Thanks Blair

syntax error (Comma) in query Expression Count(IIf(([Whelping Date] Is
Null,Null,[NEST #]))

TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]
"Marshall Barton" wrote

I want to apologize for the need for brackets.I hired a guy to do my db
and
I thought he was a genius.Then he took off and I had to learn how to do
it.Then I found these newsgroups and have since then with yawls help found
out all the things he did wrong, like the need for all those brackets and
non normalization of the tables.
But I am going to try and get someone to redo my db, now I have an idea
of
how it should be.

You have no idea how much I appreciate your help

I still get this error
syntax error (missing operator) in query '[Shed #]

UNION ALL

. . .


I need a vacation!

I skipped the last step in my tests. It seems that you can
not use UNION with a crosstab query. To get around that, we
need to create the three cross tab queries as separate saved
queries.

Really tricky set of queries, Take 3:

qrySheds:
TRANSFORM Count([NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]

qrySubTotal:
TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

qryTotal:
TRANSFORM Count([NEST #])
SELECT "Total", Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY "Total"
PIVOT [SHED #]

Finally, the query that puts it all together:
SELECT * FROM qrySheds
UNION ALL
SELECT * FROM qrySubTotal
UNION ALL
SELECT * FROM qryTotal

 

[Blair]

BINGO!!!!!!
Thanks ever so much
Blair

Arrggghhhh, Now I can't even copy and paste without blowing
it. There is an extra parenthesis in there.

Take ... I've lost count ;-):

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

Please, tell me that's a wrap ;-)
--
Marsh
MVP [MS Access]

you are getting closer qrysheds and qryTotal works
qrySubTotal has this error
Thanks Blair

syntax error (Comma) in query Expression Count(IIf(([Whelping Date] Is
Null,Null,[NEST #]))

TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]
"Marshall Barton" wrote

Blair wrote:

I want to apologize for the need for brackets.I hired a guy to do my db
and
I thought he was a genius.Then he took off and I had to learn how to do
it.Then I found these newsgroups and have since then with yawls help
found
out all the things he did wrong, like the need for all those brackets
and
non normalization of the tables.
But I am going to try and get someone to redo my db, now I have an
idea
of
how it should be.

You have no idea how much I appreciate your help

I still get this error
syntax error (missing operator) in query '[Shed #]

UNION ALL
. . .


I need a vacation!

I skipped the last step in my tests. It seems that you can
not use UNION with a crosstab query. To get around that, we
need to create the three cross tab queries as separate saved
queries.

Really tricky set of queries, Take 3:

qrySheds:
TRANSFORM Count([NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]

qrySubTotal:
TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

qryTotal:
TRANSFORM Count([NEST #])
SELECT "Total", Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY "Total"
PIVOT [SHED #]

Finally, the query that puts it all together:
SELECT * FROM qrySheds
UNION ALL
SELECT * FROM qrySubTotal
UNION ALL
SELECT * FROM qryTotal

 

[Blair]

Hi!
This has to do with the queries you wrote for me but not that they don't
work because they do.
I noticed in our communications that I had spelled WhelpingReport2 wrong. I
spelled it Whepling instead of Whelping.
My point is I went in and changed the names ( spelled them correctly) and I
am sure I have changed every thing. I even went to the extent to make a
whole new query where the criteria is controlled in a form. with that form
open and I try to open your qryshed, it tells me the jet engine doesn't
recognize it as a valid field.
Do you have any idea what's going on, or will I have to start from
scratch. something is not quite right.
Thanks Blair

Arrggghhhh, Now I can't even copy and paste without blowing
it. There is an extra parenthesis in there.

Take ... I've lost count ;-):

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

Please, tell me that's a wrap ;-)
--
Marsh
MVP [MS Access]

you are getting closer qrysheds and qryTotal works
qrySubTotal has this error
Thanks Blair

syntax error (Comma) in query Expression Count(IIf(([Whelping Date] Is
Null,Null,[NEST #]))

TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]
"Marshall Barton" wrote

Blair wrote:

I want to apologize for the need for brackets.I hired a guy to do my db
and
I thought he was a genius.Then he took off and I had to learn how to do
it.Then I found these newsgroups and have since then with yawls help
found
out all the things he did wrong, like the need for all those brackets
and
non normalization of the tables.
But I am going to try and get someone to redo my db, now I have an
idea
of
how it should be.

You have no idea how much I appreciate your help

I still get this error
syntax error (missing operator) in query '[Shed #]

UNION ALL
. . .


I need a vacation!

I skipped the last step in my tests. It seems that you can
not use UNION with a crosstab query. To get around that, we
need to create the three cross tab queries as separate saved
queries.

Really tricky set of queries, Take 3:

qrySheds:
TRANSFORM Count([NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]

qrySubTotal:
TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

qryTotal:
TRANSFORM Count([NEST #])
SELECT "Total", Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY "Total"
PIVOT [SHED #]

Finally, the query that puts it all together:
SELECT * FROM qrySheds
UNION ALL
SELECT * FROM qrySubTotal
UNION ALL
SELECT * FROM qryTotal

 

[Blair]

It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I guess I
should have used this from the start, but I was using the other until I got
the results I wanted so I wouldn't have to mess with the criteria until I
got it working the way I wanted.
Sorry for the incontinence
Thanks Blair

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND (([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Arrggghhhh, Now I can't even copy and paste without blowing
it. There is an extra parenthesis in there.

Take ... I've lost count ;-):

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

Please, tell me that's a wrap ;-)
--
Marsh
MVP [MS Access]

you are getting closer qrysheds and qryTotal works
qrySubTotal has this error
Thanks Blair

syntax error (Comma) in query Expression Count(IIf(([Whelping Date] Is
Null,Null,[NEST #]))

TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]
"Marshall Barton" wrote

Blair wrote:

I want to apologize for the need for brackets.I hired a guy to do my db
and
I thought he was a genius.Then he took off and I had to learn how to do
it.Then I found these newsgroups and have since then with yawls help
found
out all the things he did wrong, like the need for all those brackets
and
non normalization of the tables.
But I am going to try and get someone to redo my db, now I have an
idea
of
how it should be.

You have no idea how much I appreciate your help

I still get this error
syntax error (missing operator) in query '[Shed #]

UNION ALL
. . .


I need a vacation!

I skipped the last step in my tests. It seems that you can
not use UNION with a crosstab query. To get around that, we
need to create the three cross tab queries as separate saved
queries.

Really tricky set of queries, Take 3:

qrySheds:
TRANSFORM Count([NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]

qrySubTotal:
TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

qryTotal:
TRANSFORM Count([NEST #])
SELECT "Total", Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY "Total"
PIVOT [SHED #]

Finally, the query that puts it all together:
SELECT * FROM qrySheds
UNION ALL
SELECT * FROM qrySubTotal
UNION ALL
SELECT * FROM qryTotal

 

[Marshall Barton]

It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I guess I
should have used this from the start, but I was using the other until I got
the results I wanted so I wouldn't have to mess with the criteria until I
got it working the way I wanted.
Sorry for the incontinence
Thanks Blair

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND (([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

First, make sure that you have UNCHECKed the troublesome
Name AutoCorrect feature (Tools menu - Options, General
tab). This feature seems to randomly cause strange
problems. Of more immediate importance, when you change
the name of something, it changes the name of other things
with the same name.

Then double check the names of everything in the query,
especially the name of the form. (I note that you changed
the spelling of whelp , but you also seem to have dropped
the 2 at the end.)

 

[Blair]

I can't find Name AutoCorrect feature, Im using A97 if that makes a
difference. I'm pretty sure I have got all the names changed, but I will
check again.The 2 on the end was for a copy to play with like we did till I
got it working.
I have gone to the extent of making a whole new query so the names would be
right, it still didn't work.
Gona go check and do it all over again
Thanks Blair

It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I guess I
should have used this from the start, but I was using the other until I
got
the results I wanted so I wouldn't have to mess with the criteria until I
got it working the way I wanted.
Sorry for the incontinence
Thanks Blair

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

First, make sure that you have UNCHECKed the troublesome
Name AutoCorrect feature (Tools menu - Options, General
tab). This feature seems to randomly cause strange
problems. Of more immediate importance, when you change
the name of something, it changes the name of other things
with the same name.

Then double check the names of everything in the query,
especially the name of the form. (I note that you changed
the spelling of whelp , but you also seem to have dropped
the 2 at the end.)