 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2006Total a Sum Iff expression
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 Blair, What's the total SQL look like? Sam >I have this expression >Total Whelped Females: Sum(IIf([MaleReport1]![WHELPING DATE] Is Not [quoted text clipped - 5 lines] > >Thanks Blair  SignatureSam 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, > [quoted text clipped - 11 lines] > > > >Thanks Blair "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. SignatureDuane Hookom MS Access MVP > It Looks complicated to me but here it is > [quoted text clipped - 120 lines] >> > >> >Thanks 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 > "Complicated" is an understatement. You have you considered normalizing your > table structure? I would also never allow default alias names like Expr9. If [quoted text clipped - 129 lines] > >> > > >> >Thanks Blair I am trying to total [Total Females] and [Total Whelped Females] Thanks Blair > "Complicated" is an understatement. You have you considered normalizing your > table structure? I would also never allow default alias names like Expr9. If [quoted text clipped - 129 lines] > >> > > >> >Thanks Blair 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. SignatureDuane Hookom MS Access MVP >I am trying to total [Total Females] and [Total Whelped Females] > Thanks Blair [quoted text clipped - 151 lines] >> >> > >> >> >Thanks Blair Thanks I'll try that later tonight, gota take daughter horse riding Thanks Blair > I would expect a control source in a Report or Group Header or Footer > section like: [quoted text clipped - 61 lines] > >> > 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 [quoted text clipped - 9 lines] > >> > 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 [quoted text clipped - 10 lines] > >> > 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] [quoted text clipped - 22 lines] > >> > 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 [quoted text clipped - 38 lines] > >> >> > > >> >> >Thanks Blair That works Thanks Blair > I would expect a control source in a Report or Group Header or Footer > section like: [quoted text clipped - 61 lines] > >> > 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 [quoted text clipped - 9 lines] > >> > 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 [quoted text clipped - 10 lines] > >> > 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] [quoted text clipped - 22 lines] > >> > 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 [quoted text clipped - 38 lines] > >> >> > > >> >> >Thanks Blair I am trying to get my Whelp average Total Whelped Females / Total Females x 100 Thanks Blair > Blair, > [quoted text clipped - 11 lines] > > > >Thanks Blair |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.