If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Calculating totals on a Crosstab Query
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 |
#3
|
|||
|
|||
Calculating totals on a Crosstab Query
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 |
#4
|
|||
|
|||
Calculating totals on a Crosstab Query
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] |
#5
|
|||
|
|||
Calculating totals on a Crosstab Query
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] |
#6
|
|||
|
|||
Calculating totals on a Crosstab Query
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] |
#7
|
|||
|
|||
Calculating totals on a Crosstab Query
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] |
#8
|
|||
|
|||
Calculating totals on a Crosstab Query
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] |
#9
|
|||
|
|||
Calculating totals on a Crosstab Query
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. |
#10
|
|||
|
|||
Calculating totals on a Crosstab Query
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. |
Thread Tools | |
Display Modes | |
|
|