Total a Sum Iff expression

B

Blair

I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
 
O

OfficeDev18 via AccessMonster.com

Blair,

What's the total SQL look like?

Sam
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
 
B

Blair

It Looks complicated to me but here it is

SELECT MaleReport1.[SHED #], MaleReport1.[MALE NUMBER],
Count(MaleReport1.[MALE NUMBER]) AS [CountOfMALE NUMBER], [CountOfMALE
NUMBER]-[Expr9] AS Mates, Sum(MaleReport1.KITS) AS SumOfKITS,
[SumOfKITS]-[Expr10] AS KITS, IIf([Mates]<=0,0,[KITS]/[Mates]) AS [Kit Avg],
Sum(MaleReport1.[Kits Survived]) AS [SumOfKits Survived], [SumOfKits
Survived]-([Expr11]+[Expr12]) AS [Kits Survived], IIf([Mates]<=0,0,[Kits
Survived]/[Mates]) AS [Kits Sur Avg], Sum(MaleReport1.[FEMALE KITS]) AS
[SumOfFEMALE KITS], [SumOfFEMALE KITS]-[Expr11] AS [Female Kits],
IIf([Mates]<=0,0,[Female Kits]/[Mates]) AS [Female Kit Avg],
Sum(MaleReport1.[MALE KITS]) AS [SumOfMALE KITS], [SumOfMALE KITS]-[Expr12]
AS [Male Kits], IIf([Mates]<=0,0,[Male Kits]/[Mates]) AS [Male Kit Avg],
Sum(MaleReport1.Wg_grms) AS SumOfWg_grms, [SumOfWg_grms]-[Expr13] AS [Wg
gms], IIf(([Mates]-[Expr7])<=0,0,[Wg gms]/([Mates]-[Expr7])) AS [Avg Wg
gms], Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![2nd
MATING] Is Not Null And [MaleReport1]![3rd MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![4TH MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![2nd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![1st MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null And [MaleReport1]![MISSED]=True,1,0)) AS
Misses, Sum(IIf([MaleReport1]![Kits] Is Null And [MaleReport1]![WHELPING
DATE] Is Not Null,1,0)) AS Expr7, Sum(IIf([MaleReport1]![1st
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![3rd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![4TH
MATING]=[MaleReport1]![MALE NUMBER],1,0)) AS Matings,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st MATING]
Is Not Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st MATING]
Is Not Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,1,0)) AS Expr9,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not
Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null
And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null
And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![KITS],0)) AS Expr10,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![FEMALE KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Is Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![FEMALE KITS],0)) AS
Expr11, Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH MATING] Is Not Null And [MaleReport1]![4TH
MATING]<>[MaleReport1]![MALE NUMBER],[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![MALE KITS],0)) AS Expr12,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![2nd MATING] Is
Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![Wg_grms],0)) AS Expr13,
Sum(IIf([MaleReport1]![WHELPING DATE] Is Not Null,1,0)) AS [Total Whelped
Females], Sum(IIf([MaleReport1]![FEMALE #] Is Not Null,1,0)) AS [Total
Females]
FROM MaleReport1
GROUP BY MaleReport1.[SHED #], MaleReport1.[MALE NUMBER]
ORDER BY MaleReport1.[MALE NUMBER];

It is based off another Query
Thanks Blair
OfficeDev18 via AccessMonster.com said:
Blair,

What's the total SQL look like?

Sam
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
Click to expand...
 
B

Blair

I am trying to get my Whelp average
Total Whelped Females / Total Females x 100
Thanks Blair
OfficeDev18 via AccessMonster.com said:
Blair,

What's the total SQL look like?

Sam
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
Click to expand...
 
D

Duane Hookom

"Complicated" is an understatement. You have you considered normalizing your
table structure? I would also never allow default alias names like Expr9. If
it's worth calculating, it's worth giving it a decent name. You shouldn't
use an alias within another expression in your query.

Which section of the report are you placing the text box? Are you performing
an aggregate on the field from the query? If not, you will simply get a
single value from the report's record source query.

--
Duane Hookom
MS Access MVP

Blair said:
It Looks complicated to me but here it is

SELECT MaleReport1.[SHED #], MaleReport1.[MALE NUMBER],
Count(MaleReport1.[MALE NUMBER]) AS [CountOfMALE NUMBER], [CountOfMALE
NUMBER]-[Expr9] AS Mates, Sum(MaleReport1.KITS) AS SumOfKITS,
[SumOfKITS]-[Expr10] AS KITS, IIf([Mates]<=0,0,[KITS]/[Mates]) AS [Kit
Avg],
Sum(MaleReport1.[Kits Survived]) AS [SumOfKits Survived], [SumOfKits
Survived]-([Expr11]+[Expr12]) AS [Kits Survived], IIf([Mates]<=0,0,[Kits
Survived]/[Mates]) AS [Kits Sur Avg], Sum(MaleReport1.[FEMALE KITS]) AS
[SumOfFEMALE KITS], [SumOfFEMALE KITS]-[Expr11] AS [Female Kits],
IIf([Mates]<=0,0,[Female Kits]/[Mates]) AS [Female Kit Avg],
Sum(MaleReport1.[MALE KITS]) AS [SumOfMALE KITS], [SumOfMALE
KITS]-[Expr12]
AS [Male Kits], IIf([Mates]<=0,0,[Male Kits]/[Mates]) AS [Male Kit Avg],
Sum(MaleReport1.Wg_grms) AS SumOfWg_grms, [SumOfWg_grms]-[Expr13] AS [Wg
gms], IIf(([Mates]-[Expr7])<=0,0,[Wg gms]/([Mates]-[Expr7])) AS [Avg Wg
gms], Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![2nd
MATING] Is Not Null And [MaleReport1]![3rd MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![4TH MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![2nd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![1st MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null And [MaleReport1]![MISSED]=True,1,0))
AS
Misses, Sum(IIf([MaleReport1]![Kits] Is Null And [MaleReport1]![WHELPING
DATE] Is Not Null,1,0)) AS Expr7, Sum(IIf([MaleReport1]![1st
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![3rd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![4TH
MATING]=[MaleReport1]![MALE NUMBER],1,0)) AS Matings,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![3rd MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![2nd MATING] Is Null,1,0)) AS Expr9,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not
Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![KITS],0)) AS Expr10,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![FEMALE KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Is Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![FEMALE KITS],0)) AS
Expr11, Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH MATING] Is Not Null And [MaleReport1]![4TH
MATING]<>[MaleReport1]![MALE NUMBER],[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![MALE KITS],0)) AS
Expr12,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![2nd MATING] Is
Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![Wg_grms],0)) AS Expr13,
Sum(IIf([MaleReport1]![WHELPING DATE] Is Not Null,1,0)) AS [Total Whelped
Females], Sum(IIf([MaleReport1]![FEMALE #] Is Not Null,1,0)) AS [Total
Females]
FROM MaleReport1
GROUP BY MaleReport1.[SHED #], MaleReport1.[MALE NUMBER]
ORDER BY MaleReport1.[MALE NUMBER];

It is based off another Query
Thanks Blair
OfficeDev18 via AccessMonster.com said:
Blair,

What's the total SQL look like?

Sam
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
Click to expand...
Click to expand...
 
B

Blair

I agree completely with you NOW! but when I HAD to take over someone else's
db I knew NOTHING! and not much more now. I know it needs normalizing, but
not sure what it means. At the time I did this report I was impressed I even
done it, but now I have already realized what you said about using default
alias names like Expr9. I have done a lot of stupid things like that but now
it is a lot of work to change things and get them all changed so it doesn't
mess up other things.
Are you performing
an aggregate on the field from the query?
I don't understand what you are asking because I have never learned these
terms because I only work on the db when I have to and want to do something
different. I am using the report footer for the textbox.
I am looking into getting someone professional to redo my db right.
This is the sql of the first query
SELECT [98MaleHistory].[MALE NUMBER], [98MatingRecords].[SHED #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[1st MATING], [98MatingRecords].[2nd MATING],
[98MatingRecords].[3rd MATING], [98MatingRecords].[4TH MATING],
[98MatingRecords].KITS, [98MatingRecords].[Kits Survived],
[98MatingRecords].[FEMALE KITS], [98MatingRecords].[MALE KITS],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].MISSED,
[98MatingRecords].Try, [98MatingRecords].Wg_grms
FROM 98MaleHistory, 98MatingRecords
GROUP BY [98MaleHistory].[MALE NUMBER], [98MatingRecords].[SHED #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[1st MATING], [98MatingRecords].[2nd MATING],
[98MatingRecords].[3rd MATING], [98MatingRecords].[4TH MATING],
[98MatingRecords].KITS, [98MatingRecords].[Kits Survived],
[98MatingRecords].[FEMALE KITS], [98MatingRecords].[MALE KITS],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].MISSED,
[98MatingRecords].Try, [98MatingRecords].Wg_grms
HAVING ((([98MaleHistory].[MALE NUMBER])=[98MatingRecords].[1st MATING] Or
([98MaleHistory].[MALE NUMBER])=[98MatingRecords].[2nd MATING] Or
([98MaleHistory].[MALE NUMBER])=[98MatingRecords].[3rd MATING] Or
([98MaleHistory].[MALE NUMBER])=[98MatingRecords].[4TH MATING]) AND
(([98MatingRecords].[SHED #]) Like
IIf((IsNull([Forms]![MalePerformanceReport]![Text0])),"*",[Forms]![MalePerfo
rmanceReport]![Text0])) AND (([98MatingRecords].[Mating
Year])=[Forms]![MalePerformanceReport]![Text2]))
ORDER BY [98MaleHistory].[MALE NUMBER];
Thanks for any help
Blair
Duane Hookom said:
"Complicated" is an understatement. You have you considered normalizing your
table structure? I would also never allow default alias names like Expr9. If
it's worth calculating, it's worth giving it a decent name. You shouldn't
use an alias within another expression in your query.

Which section of the report are you placing the text box? Are you performing
an aggregate on the field from the query? If not, you will simply get a
single value from the report's record source query.

--
Duane Hookom
MS Access MVP

Blair said:
It Looks complicated to me but here it is

SELECT MaleReport1.[SHED #], MaleReport1.[MALE NUMBER],
Count(MaleReport1.[MALE NUMBER]) AS [CountOfMALE NUMBER], [CountOfMALE
NUMBER]-[Expr9] AS Mates, Sum(MaleReport1.KITS) AS SumOfKITS,
[SumOfKITS]-[Expr10] AS KITS, IIf([Mates]<=0,0,[KITS]/[Mates]) AS [Kit
Avg],
Sum(MaleReport1.[Kits Survived]) AS [SumOfKits Survived], [SumOfKits
Survived]-([Expr11]+[Expr12]) AS [Kits Survived], IIf([Mates]<=0,0,[Kits
Survived]/[Mates]) AS [Kits Sur Avg], Sum(MaleReport1.[FEMALE KITS]) AS
[SumOfFEMALE KITS], [SumOfFEMALE KITS]-[Expr11] AS [Female Kits],
IIf([Mates]<=0,0,[Female Kits]/[Mates]) AS [Female Kit Avg],
Sum(MaleReport1.[MALE KITS]) AS [SumOfMALE KITS], [SumOfMALE
KITS]-[Expr12]
AS [Male Kits], IIf([Mates]<=0,0,[Male Kits]/[Mates]) AS [Male Kit Avg],
Sum(MaleReport1.Wg_grms) AS SumOfWg_grms, [SumOfWg_grms]-[Expr13] AS [Wg
gms], IIf(([Mates]-[Expr7])<=0,0,[Wg gms]/([Mates]-[Expr7])) AS [Avg Wg
gms], Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![2nd
MATING] Is Not Null And [MaleReport1]![3rd MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![4TH MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![2nd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![1st MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null And [MaleReport1]![MISSED]=True,1,0))
AS
Misses, Sum(IIf([MaleReport1]![Kits] Is Null And [MaleReport1]![WHELPING
DATE] Is Not Null,1,0)) AS Expr7, Sum(IIf([MaleReport1]![1st
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![3rd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![4TH
MATING]=[MaleReport1]![MALE NUMBER],1,0)) AS Matings,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![3rd MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![2nd MATING] Is Null,1,0)) AS Expr9,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not
Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![KITS],0)) AS Expr10,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![FEMALE KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Is Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![FEMALE KITS],0)) AS
Expr11, Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH MATING] Is Not Null And [MaleReport1]![4TH
MATING]<>[MaleReport1]![MALE NUMBER],[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![MALE KITS],0)) AS
Expr12,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![2nd MATING] Is
Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![Wg_grms],0)) AS Expr13,
Sum(IIf([MaleReport1]![WHELPING DATE] Is Not Null,1,0)) AS [Total Whelped
Females], Sum(IIf([MaleReport1]![FEMALE #] Is Not Null,1,0)) AS [Total
Females]
FROM MaleReport1
GROUP BY MaleReport1.[SHED #], MaleReport1.[MALE NUMBER]
ORDER BY MaleReport1.[MALE NUMBER];

It is based off another Query
Thanks Blair
OfficeDev18 via AccessMonster.com said:
Blair,

What's the total SQL look like?

Sam

Blair wrote:
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
Click to expand...
Click to expand...
 
B

Blair

I am trying to total [Total Females] and [Total Whelped Females]
Thanks Blair
Duane Hookom said:
"Complicated" is an understatement. You have you considered normalizing your
table structure? I would also never allow default alias names like Expr9. If
it's worth calculating, it's worth giving it a decent name. You shouldn't
use an alias within another expression in your query.

Which section of the report are you placing the text box? Are you performing
an aggregate on the field from the query? If not, you will simply get a
single value from the report's record source query.

--
Duane Hookom
MS Access MVP

Blair said:
It Looks complicated to me but here it is

SELECT MaleReport1.[SHED #], MaleReport1.[MALE NUMBER],
Count(MaleReport1.[MALE NUMBER]) AS [CountOfMALE NUMBER], [CountOfMALE
NUMBER]-[Expr9] AS Mates, Sum(MaleReport1.KITS) AS SumOfKITS,
[SumOfKITS]-[Expr10] AS KITS, IIf([Mates]<=0,0,[KITS]/[Mates]) AS [Kit
Avg],
Sum(MaleReport1.[Kits Survived]) AS [SumOfKits Survived], [SumOfKits
Survived]-([Expr11]+[Expr12]) AS [Kits Survived], IIf([Mates]<=0,0,[Kits
Survived]/[Mates]) AS [Kits Sur Avg], Sum(MaleReport1.[FEMALE KITS]) AS
[SumOfFEMALE KITS], [SumOfFEMALE KITS]-[Expr11] AS [Female Kits],
IIf([Mates]<=0,0,[Female Kits]/[Mates]) AS [Female Kit Avg],
Sum(MaleReport1.[MALE KITS]) AS [SumOfMALE KITS], [SumOfMALE
KITS]-[Expr12]
AS [Male Kits], IIf([Mates]<=0,0,[Male Kits]/[Mates]) AS [Male Kit Avg],
Sum(MaleReport1.Wg_grms) AS SumOfWg_grms, [SumOfWg_grms]-[Expr13] AS [Wg
gms], IIf(([Mates]-[Expr7])<=0,0,[Wg gms]/([Mates]-[Expr7])) AS [Avg Wg
gms], Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![2nd
MATING] Is Not Null And [MaleReport1]![3rd MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![4TH MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![2nd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![1st MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null And [MaleReport1]![MISSED]=True,1,0))
AS
Misses, Sum(IIf([MaleReport1]![Kits] Is Null And [MaleReport1]![WHELPING
DATE] Is Not Null,1,0)) AS Expr7, Sum(IIf([MaleReport1]![1st
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![3rd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![4TH
MATING]=[MaleReport1]![MALE NUMBER],1,0)) AS Matings,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![3rd MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![2nd MATING] Is Null,1,0)) AS Expr9,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not
Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![KITS],0)) AS Expr10,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![FEMALE KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Is Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![FEMALE KITS],0)) AS
Expr11, Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH MATING] Is Not Null And [MaleReport1]![4TH
MATING]<>[MaleReport1]![MALE NUMBER],[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![MALE KITS],0)) AS
Expr12,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![2nd MATING] Is
Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![Wg_grms],0)) AS Expr13,
Sum(IIf([MaleReport1]![WHELPING DATE] Is Not Null,1,0)) AS [Total Whelped
Females], Sum(IIf([MaleReport1]![FEMALE #] Is Not Null,1,0)) AS [Total
Females]
FROM MaleReport1
GROUP BY MaleReport1.[SHED #], MaleReport1.[MALE NUMBER]
ORDER BY MaleReport1.[MALE NUMBER];

It is based off another Query
Thanks Blair
OfficeDev18 via AccessMonster.com said:
Blair,

What's the total SQL look like?

Sam

Blair wrote:
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I would expect a control source in a Report or Group Header or Footer
section like:
=Sum([Total Females])
=Sum([Total Whelped Females])
Sum(), Avg(), Count() are aggregate functions.
--
Duane Hookom
MS Access MVP

Blair said:
I am trying to total [Total Females] and [Total Whelped Females]
Thanks Blair
Duane Hookom said:
"Complicated" is an understatement. You have you considered normalizing your
table structure? I would also never allow default alias names like Expr9. If
it's worth calculating, it's worth giving it a decent name. You shouldn't
use an alias within another expression in your query.

Which section of the report are you placing the text box? Are you performing
an aggregate on the field from the query? If not, you will simply get a
single value from the report's record source query.

--
Duane Hookom
MS Access MVP

Blair said:
It Looks complicated to me but here it is

SELECT MaleReport1.[SHED #], MaleReport1.[MALE NUMBER],
Count(MaleReport1.[MALE NUMBER]) AS [CountOfMALE NUMBER], [CountOfMALE
NUMBER]-[Expr9] AS Mates, Sum(MaleReport1.KITS) AS SumOfKITS,
[SumOfKITS]-[Expr10] AS KITS, IIf([Mates]<=0,0,[KITS]/[Mates]) AS [Kit
Avg],
Sum(MaleReport1.[Kits Survived]) AS [SumOfKits Survived], [SumOfKits
Survived]-([Expr11]+[Expr12]) AS [Kits Survived],
IIf([Mates]<=0,0,[Kits
Survived]/[Mates]) AS [Kits Sur Avg], Sum(MaleReport1.[FEMALE KITS]) AS
[SumOfFEMALE KITS], [SumOfFEMALE KITS]-[Expr11] AS [Female Kits],
IIf([Mates]<=0,0,[Female Kits]/[Mates]) AS [Female Kit Avg],
Sum(MaleReport1.[MALE KITS]) AS [SumOfMALE KITS], [SumOfMALE
KITS]-[Expr12]
AS [Male Kits], IIf([Mates]<=0,0,[Male Kits]/[Mates]) AS [Male Kit
Avg],
Sum(MaleReport1.Wg_grms) AS SumOfWg_grms, [SumOfWg_grms]-[Expr13] AS
[Wg
gms], IIf(([Mates]-[Expr7])<=0,0,[Wg gms]/([Mates]-[Expr7])) AS [Avg Wg
gms], Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]=[MaleReport1]![MALE
NUMBER] And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![2nd
MATING] Is Not Null And [MaleReport1]![3rd MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![4TH MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![2nd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![1st MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))
AS
Misses, Sum(IIf([MaleReport1]![Kits] Is Null And
[MaleReport1]![WHELPING
DATE] Is Not Null,1,0)) AS Expr7, Sum(IIf([MaleReport1]![1st
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![3rd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![4TH
MATING]=[MaleReport1]![MALE NUMBER],1,0)) AS Matings,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![3rd MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![2nd MATING] Is Null,1,0)) AS Expr9,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not
Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![KITS],0)) AS Expr10,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![FEMALE KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Is Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![FEMALE KITS],0)) AS
Expr11, Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH MATING] Is Not Null And [MaleReport1]![4TH
MATING]<>[MaleReport1]![MALE NUMBER],[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![MALE KITS],0)) AS
Expr12,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]<>[MaleReport1]![MALE
NUMBER],[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![2nd MATING] Is
Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![Wg_grms],0)) AS Expr13,
Sum(IIf([MaleReport1]![WHELPING DATE] Is Not Null,1,0)) AS [Total Whelped
Females], Sum(IIf([MaleReport1]![FEMALE #] Is Not Null,1,0)) AS [Total
Females]
FROM MaleReport1
GROUP BY MaleReport1.[SHED #], MaleReport1.[MALE NUMBER]
ORDER BY MaleReport1.[MALE NUMBER];

It is based off another Query
Thanks Blair
Blair,

What's the total SQL look like?

Sam

Blair wrote:
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
Click to expand...
Click to expand...
 
B

Blair

Thanks I'll try that later tonight, gota take daughter horse riding
Thanks Blair
Duane Hookom said:
I would expect a control source in a Report or Group Header or Footer
section like:
=Sum([Total Females])
=Sum([Total Whelped Females])
Sum(), Avg(), Count() are aggregate functions.
--
Duane Hookom
MS Access MVP

Blair said:
I am trying to total [Total Females] and [Total Whelped Females]
Thanks Blair
Duane Hookom said:
"Complicated" is an understatement. You have you considered normalizing your
table structure? I would also never allow default alias names like
Click to expand...
Expr9.
If
it's worth calculating, it's worth giving it a decent name. You shouldn't
use an alias within another expression in your query.

Which section of the report are you placing the text box? Are you performing
an aggregate on the field from the query? If not, you will simply get a
single value from the report's record source query.

--
Duane Hookom
MS Access MVP

It Looks complicated to me but here it is

SELECT MaleReport1.[SHED #], MaleReport1.[MALE NUMBER],
Count(MaleReport1.[MALE NUMBER]) AS [CountOfMALE NUMBER], [CountOfMALE
NUMBER]-[Expr9] AS Mates, Sum(MaleReport1.KITS) AS SumOfKITS,
[SumOfKITS]-[Expr10] AS KITS, IIf([Mates]<=0,0,[KITS]/[Mates]) AS [Kit
Avg],
Sum(MaleReport1.[Kits Survived]) AS [SumOfKits Survived], [SumOfKits
Survived]-([Expr11]+[Expr12]) AS [Kits Survived],
IIf([Mates]<=0,0,[Kits
Survived]/[Mates]) AS [Kits Sur Avg], Sum(MaleReport1.[FEMALE KITS]) AS
[SumOfFEMALE KITS], [SumOfFEMALE KITS]-[Expr11] AS [Female Kits],
IIf([Mates]<=0,0,[Female Kits]/[Mates]) AS [Female Kit Avg],
Sum(MaleReport1.[MALE KITS]) AS [SumOfMALE KITS], [SumOfMALE
KITS]-[Expr12]
AS [Male Kits], IIf([Mates]<=0,0,[Male Kits]/[Mates]) AS [Male Kit
Avg],
Sum(MaleReport1.Wg_grms) AS SumOfWg_grms, [SumOfWg_grms]-[Expr13] AS
[Wg
gms], IIf(([Mates]-[Expr7])<=0,0,[Wg gms]/([Mates]-[Expr7])) AS [Avg Wg
gms], Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]=[MaleReport1]![MALE
NUMBER] And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![2nd
MATING] Is Not Null And [MaleReport1]![3rd MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![4TH MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![2nd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![1st MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))
AS
Misses, Sum(IIf([MaleReport1]![Kits] Is Null And
[MaleReport1]![WHELPING
DATE] Is Not Null,1,0)) AS Expr7, Sum(IIf([MaleReport1]![1st
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![3rd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![4TH
MATING]=[MaleReport1]![MALE NUMBER],1,0)) AS Matings,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH
Click to expand...
MATING] said:
NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![3rd MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![2nd MATING] Is Null,1,0)) AS Expr9,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH
Click to expand...
MATING] said:
NUMBER],[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Click to expand...
Is
Not
Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![KITS],0)) AS Expr10,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH
Click to expand...
MATING] said:
NUMBER],[MaleReport1]![FEMALE KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Is Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![FEMALE KITS],0)) AS
Expr11, Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH MATING] Is Not Null And [MaleReport1]![4TH
MATING]<>[MaleReport1]![MALE NUMBER],[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![MALE KITS],0)) AS
Expr12,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH
Click to expand...
MATING] said:
NUMBER],[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![2nd
Click to expand...
MATING]
Is
Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING]
Click to expand...
Is
Not
Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING]
Click to expand...
Is
Not
Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![Wg_grms],0)) AS Expr13,
Sum(IIf([MaleReport1]![WHELPING DATE] Is Not Null,1,0)) AS [Total Whelped
Females], Sum(IIf([MaleReport1]![FEMALE #] Is Not Null,1,0)) AS [Total
Females]
FROM MaleReport1
GROUP BY MaleReport1.[SHED #], MaleReport1.[MALE NUMBER]
ORDER BY MaleReport1.[MALE NUMBER];

It is based off another Query
Thanks Blair
Blair,

What's the total SQL look like?

Sam

Blair wrote:
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
Click to expand...
Click to expand...
 
B

Blair

That works
Thanks Blair
Duane Hookom said:
I would expect a control source in a Report or Group Header or Footer
section like:
=Sum([Total Females])
=Sum([Total Whelped Females])
Sum(), Avg(), Count() are aggregate functions.
--
Duane Hookom
MS Access MVP

Blair said:
I am trying to total [Total Females] and [Total Whelped Females]
Thanks Blair
Duane Hookom said:
"Complicated" is an understatement. You have you considered normalizing your
table structure? I would also never allow default alias names like
Click to expand...
Expr9.
If
it's worth calculating, it's worth giving it a decent name. You shouldn't
use an alias within another expression in your query.

Which section of the report are you placing the text box? Are you performing
an aggregate on the field from the query? If not, you will simply get a
single value from the report's record source query.

--
Duane Hookom
MS Access MVP

It Looks complicated to me but here it is

SELECT MaleReport1.[SHED #], MaleReport1.[MALE NUMBER],
Count(MaleReport1.[MALE NUMBER]) AS [CountOfMALE NUMBER], [CountOfMALE
NUMBER]-[Expr9] AS Mates, Sum(MaleReport1.KITS) AS SumOfKITS,
[SumOfKITS]-[Expr10] AS KITS, IIf([Mates]<=0,0,[KITS]/[Mates]) AS [Kit
Avg],
Sum(MaleReport1.[Kits Survived]) AS [SumOfKits Survived], [SumOfKits
Survived]-([Expr11]+[Expr12]) AS [Kits Survived],
IIf([Mates]<=0,0,[Kits
Survived]/[Mates]) AS [Kits Sur Avg], Sum(MaleReport1.[FEMALE KITS]) AS
[SumOfFEMALE KITS], [SumOfFEMALE KITS]-[Expr11] AS [Female Kits],
IIf([Mates]<=0,0,[Female Kits]/[Mates]) AS [Female Kit Avg],
Sum(MaleReport1.[MALE KITS]) AS [SumOfMALE KITS], [SumOfMALE
KITS]-[Expr12]
AS [Male Kits], IIf([Mates]<=0,0,[Male Kits]/[Mates]) AS [Male Kit
Avg],
Sum(MaleReport1.Wg_grms) AS SumOfWg_grms, [SumOfWg_grms]-[Expr13] AS
[Wg
gms], IIf(([Mates]-[Expr7])<=0,0,[Wg gms]/([Mates]-[Expr7])) AS [Avg Wg
gms], Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH MATING]=[MaleReport1]![MALE
NUMBER] And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![2nd
MATING] Is Not Null And [MaleReport1]![3rd MATING]=[MaleReport1]![MALE
NUMBER] And [MaleReport1]![4TH MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![2nd MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))+Sum(IIf([MaleReport1]![1st MATING] Is
Not
Null And [MaleReport1]![1st MATING]=[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null And
[MaleReport1]![MISSED]=True,1,0))
AS
Misses, Sum(IIf([MaleReport1]![Kits] Is Null And
[MaleReport1]![WHELPING
DATE] Is Not Null,1,0)) AS Expr7, Sum(IIf([MaleReport1]![1st
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![3rd
MATING]=[MaleReport1]![MALE NUMBER],1,0))+Sum(IIf([MaleReport1]![4TH
MATING]=[MaleReport1]![MALE NUMBER],1,0)) AS Matings,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH
Click to expand...
MATING] said:
NUMBER],1,0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![3rd MATING] Is Null,1,0))+Sum(IIf([MaleReport1]![1st
MATING]
Is Not Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![2nd MATING] Is Null,1,0)) AS Expr9,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH
Click to expand...
MATING] said:
NUMBER],[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Click to expand...
Is
Not
Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not
Null
And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![KITS],0)) AS Expr10,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH
Click to expand...
MATING] said:
NUMBER],[MaleReport1]![FEMALE KITS],0))+Sum(IIf([MaleReport1]![2nd MATING]
Is Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![FEMALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![FEMALE KITS],0)) AS
Expr11, Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And
[MaleReport1]![4TH MATING] Is Not Null And [MaleReport1]![4TH
MATING]<>[MaleReport1]![MALE NUMBER],[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![2nd MATING] Is Not Null And
[MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![4TH MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is Null,[MaleReport1]![MALE
KITS],0))+Sum(IIf([MaleReport1]![1st MATING] Is Not Null And
[MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![MALE KITS],0)) AS
Expr12,
Sum(IIf([MaleReport1]![3rd MATING] Is Not Null And [MaleReport1]![4TH
MATING] Is Not Null And [MaleReport1]![4TH
Click to expand...
MATING] said:
NUMBER],[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![2nd
Click to expand...
MATING]
Is
Not Null And [MaleReport1]![3rd MATING]<>[MaleReport1]![MALE NUMBER]
And
[MaleReport1]![4TH MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING]
Click to expand...
Is
Not
Null And [MaleReport1]![2nd MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![3rd MATING] Is
Null,[MaleReport1]![Wg_grms],0))+Sum(IIf([MaleReport1]![1st MATING]
Click to expand...
Is
Not
Null And [MaleReport1]![1st MATING]<>[MaleReport1]![MALE NUMBER] And
[MaleReport1]![2nd MATING] Is Null,[MaleReport1]![Wg_grms],0)) AS Expr13,
Sum(IIf([MaleReport1]![WHELPING DATE] Is Not Null,1,0)) AS [Total Whelped
Females], Sum(IIf([MaleReport1]![FEMALE #] Is Not Null,1,0)) AS [Total
Females]
FROM MaleReport1
GROUP BY MaleReport1.[SHED #], MaleReport1.[MALE NUMBER]
ORDER BY MaleReport1.[MALE NUMBER];

It is based off another Query
Thanks Blair
Blair,

What's the total SQL look like?

Sam

Blair wrote:
I have this expression
Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not
Null,1,0))

It gives me the sum for each record, which there are 54 records and the
total of the sums would be 337. How do I get this to total? When I drag
field into report I get 9 which is the sum of the first row, I want 337

Thanks Blair
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Eliminate duplicates from expression 2
Running sum expression 2
Tring to create a sum expression while excluding any duplicates 2
Help Needed: Create Sum in Report Footer based on aggregate expression 1
if a # then sum, if text leave null 2
IIF in Crosstab Expression 0
Crosstab query totals 0
Need the SUM TOTAL in Hours for Time Out-Time In 2

Top