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 |
#11
|
|||
|
|||
IIf statement
The following untested SQL might work for you. IT may or may not work, I
didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#12
|
|||
|
|||
IIf statement
Mr. John Spencer, thank you very much! That did exactly everything I needed.
You are a genius! Now all I need to do is to somehow create a Make-table query possibly to combine the results with the original data in my table. -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#13
|
|||
|
|||
IIf statement
Sorry, it seemed to work great at first, but when I added a row of data it
did not take the max of the two values based on a null [Qual]. I copied the first row of data, changed the [Result] value to 2, and deleted the "ND" from the [Qual] to make it null, saved the table and reran the query which resulted a [Result] of 2.4 (a sum of both rows of data) rather than using a maximum or single value (ideally the one with the null [Qual]. Is there a way to have the query also search for null [Qual] values first to use for the [Result], and if none found then use the [Result] values with a "not null" [Qual]? If that can be done, then I think I can be done :-) -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#14
|
|||
|
|||
IIf statement
John, do you think there is a way to add something in the body of your
expression, "...WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult..." so that it will Sum(Result) only IF Qual is Null and/or Max(Result) only IF Qual is not null? -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#15
|
|||
|
|||
IIf statement
I am confused about what you want to do. What are the rules that you want
to use? Are your rules? If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. If any lab_Id group has all quals null then get the sum of the Results Or are they something else? This will get the Max result of only those records where Qual has a value. Max(IIF(Qual is Null, Null,Result)) This will get the SUM of Result where Qual has no value SUM(IIF(Qual is Null,Null, Result)) But before I propose anything else, I would want to know the exact rules that should be used to do the calculation. "Barry" wrote in message ... John, do you think there is a way to add something in the body of your expression, "...WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult..." so that it will Sum(Result) only IF Qual is Null and/or Max(Result) only IF Qual is not null? -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#16
|
|||
|
|||
IIf statement
Yes, you are right, except If any lab_Id group has ANY quals null then get
the sum of the Results (and would like for it to first check for this, as the higher priority). Then check If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. Thanks -- Barry Guidry "John Spencer" wrote: I am confused about what you want to do. What are the rules that you want to use? Are your rules? If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. If any lab_Id group has all quals null then get the sum of the Results Or are they something else? This will get the Max result of only those records where Qual has a value. Max(IIF(Qual is Null, Null,Result)) This will get the SUM of Result where Qual has no value SUM(IIF(Qual is Null,Null, Result)) But before I propose anything else, I would want to know the exact rules that should be used to do the calculation. "Barry" wrote in message ... John, do you think there is a way to add something in the body of your expression, "...WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult..." so that it will Sum(Result) only IF Qual is Null and/or Max(Result) only IF Qual is not null? -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#17
|
|||
|
|||
IIf statement
Example: If[Qual] contains a null value to sum[Result] first, or else
max[Result] if no null values found in [Qual], all the while performing the operation on each [Label_id] group. I know what I am looking for here, but I don't know how to write the expression. -- Barry Guidry "John Spencer" wrote: I am confused about what you want to do. What are the rules that you want to use? Are your rules? If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. If any lab_Id group has all quals null then get the sum of the Results Or are they something else? This will get the Max result of only those records where Qual has a value. Max(IIF(Qual is Null, Null,Result)) This will get the SUM of Result where Qual has no value SUM(IIF(Qual is Null,Null, Result)) But before I propose anything else, I would want to know the exact rules that should be used to do the calculation. "Barry" wrote in message ... John, do you think there is a way to add something in the body of your expression, "...WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult..." so that it will Sum(Result) only IF Qual is Null and/or Max(Result) only IF Qual is not null? -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#18
|
|||
|
|||
IIf statement
Still not clear.
Lab Group has a record with qual as Null, ---sum ALL the results for that lab group or ---sum only the results that have null ** Lab Group has a record with Qual that has a value ---get max of ALL the results for that lab group or ---get max of only the results that have a value in qual ** Assuming that you want the ones marked with **, I would try the following. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , SUM(IIF(Qual is Null,Null, Result)) , Max(IIF(Qual is Null, Null,Result))) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID "Barry" wrote in message ... Yes, you are right, except If any lab_Id group has ANY quals null then get the sum of the Results (and would like for it to first check for this, as the higher priority). Then check If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. Thanks -- Barry Guidry "John Spencer" wrote: I am confused about what you want to do. What are the rules that you want to use? Are your rules? If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. If any lab_Id group has all quals null then get the sum of the Results Or are they something else? This will get the Max result of only those records where Qual has a value. Max(IIF(Qual is Null, Null,Result)) This will get the SUM of Result where Qual has no value SUM(IIF(Qual is Null,Null, Result)) But before I propose anything else, I would want to know the exact rules that should be used to do the calculation. "Barry" wrote in message ... John, do you think there is a way to add something in the body of your expression, "...WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult..." so that it will Sum(Result) only IF Qual is Null and/or Max(Result) only IF Qual is not null? -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#19
|
|||
|
|||
IIf statement
John, based on your quoted post below:
Lab Group has a record with qual as Null, ---sum ALL the results for that lab group or ---sum only the results that have null ** (this one and this one first as it takes priority) Lab Group has a record with Qual that has a value ---get max of ALL the results for that lab group or ---get max of only the results that have a value in qual ** (this one only if the above result is not met) -- Barry Guidry "John Spencer" wrote: Still not clear. Lab Group has a record with qual as Null, ---sum ALL the results for that lab group or ---sum only the results that have null ** Lab Group has a record with Qual that has a value ---get max of ALL the results for that lab group or ---get max of only the results that have a value in qual ** Assuming that you want the ones marked with **, I would try the following. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , SUM(IIF(Qual is Null,Null, Result)) , Max(IIF(Qual is Null, Null,Result))) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID "Barry" wrote in message ... Yes, you are right, except If any lab_Id group has ANY quals null then get the sum of the Results (and would like for it to first check for this, as the higher priority). Then check If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. Thanks -- Barry Guidry "John Spencer" wrote: I am confused about what you want to do. What are the rules that you want to use? Are your rules? If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. If any lab_Id group has all quals null then get the sum of the Results Or are they something else? This will get the Max result of only those records where Qual has a value. Max(IIF(Qual is Null, Null,Result)) This will get the SUM of Result where Qual has no value SUM(IIF(Qual is Null,Null, Result)) But before I propose anything else, I would want to know the exact rules that should be used to do the calculation. "Barry" wrote in message ... John, do you think there is a way to add something in the body of your expression, "...WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult..." so that it will Sum(Result) only IF Qual is Null and/or Max(Result) only IF Qual is not null? -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
#20
|
|||
|
|||
IIf statement
I think it would somehow take a combination of the expression you first
suggested and one such as this "select iif(isnull(Qual, sum(Result), max(Result))) from table" -- Barry Guidry "John Spencer" wrote: Still not clear. Lab Group has a record with qual as Null, ---sum ALL the results for that lab group or ---sum only the results that have null ** Lab Group has a record with Qual that has a value ---get max of ALL the results for that lab group or ---get max of only the results that have a value in qual ** Assuming that you want the ones marked with **, I would try the following. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , SUM(IIF(Qual is Null,Null, Result)) , Max(IIF(Qual is Null, Null,Result))) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID "Barry" wrote in message ... Yes, you are right, except If any lab_Id group has ANY quals null then get the sum of the Results (and would like for it to first check for this, as the higher priority). Then check If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. Thanks -- Barry Guidry "John Spencer" wrote: I am confused about what you want to do. What are the rules that you want to use? Are your rules? If any Lab_ID group has qual that is not null then get the max of the Result where the qual is not null. If any lab_Id group has all quals null then get the sum of the Results Or are they something else? This will get the Max result of only those records where Qual has a value. Max(IIF(Qual is Null, Null,Result)) This will get the SUM of Result where Qual has no value SUM(IIF(Qual is Null,Null, Result)) But before I propose anything else, I would want to know the exact rules that should be used to do the calculation. "Barry" wrote in message ... John, do you think there is a way to add something in the body of your expression, "...WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult..." so that it will Sum(Result) only IF Qual is Null and/or Max(Result) only IF Qual is not null? -- Barry Guidry "John Spencer" wrote: The following untested SQL might work for you. IT may or may not work, I didn't have a way to test it. SELECT LAB_ID , IIF(Exists( SELECT * FROM YourTable as T2 WHERE Qual Is Null and T2.Lab_ID = T1.Lab_ID) , Sum(Result) , Max(Result)) as CalcResult FROM TheTable as T1 GROUP BY T1.LAB_ID That checks to see if any value in the Qual field for a lab_id is Null. If at least one value is null, then it SUMS all the values. If none of the values are null then it gets the Max. IF I have misunderstood your requirement, then I apologize for wasting your time. "Barry" wrote in message news Duane, that returned one value of "2800" from one group of [Lab_id] in which [Qual] was a null value. This is a start but the idea was to have it sum both values of the [Lab_id] group "711723" which would have been "2800" + "1900"="4700". You know, as I pondered over my given situation some more, I have come to believe that this will be impossible in Access due to the simple fact that when we usually perform this operation in Excel we must make an intelligent decision as to a priority of summing the [Result] values if there is any null value in [Qual] within the [Lab_id] group, rather than taking the max of the [Result] if all [Qual] values in a [Lab_id] group are not null. You see, I do not see if it will even be possible to set a priority such as this in Access (to first search [Qual] for a null value and sum rather than max the [Result]). This is an example of the test data: NAME DATE LAB_ID ANALYTE RESULT QUAL RW-6 2/24/2006 711721 o-Xylene 0.4 ND RW-5 2/24/2006 711722 o-Xylene 0.4 ND RW-1 2/24/2006 711723 m+p-Xylene 2800 RW-1 2/24/2006 711723 o-Xylene 1900 Duplicate 2/24/2006 711724 m+p-Xylene 2500 Duplicate 2/24/2006 711724 o-Xylene 1700 -- Barry Guidry "Duane Hookom" wrote: You might want to use this as a starting point: SELECT Lab_ID, IIf(IsNull(Max([Qual])),Sum([Result]),Max([Result])) AS Expr1 FROM Xylenes GROUP BY Xylenes.Lab_ID; -- Duane Hookom Microsoft Access MVP "Barry" wrote: I am trying a SQL statement such as "SELECT Max(IIf([Qual] Is Not Null,[Result],0), Sum(IIf([Qual] Is Null,[Result],0)) AS Expr1 FROM xylenes;" to try to combine the two actions (sum or max) but it is returning an error that I have a syntax error, missing operator. I have never queried in SQL View, so any advice as to how I can correct the above type of action would be appreciated. -- Barry Guidry "Duane Hookom" wrote: If I understand correctly, Allen Browne's suggestion should work. It's difficult to determine since you included a data record 4... ...2222 that seems to be ignored and the "4" is the same as the result you are expecting from the Result column. -- Duane Hookom Microsoft Access MVP "Barry" wrote: Yes, Duane you are right...the null field is based on each record, but I would like the statement to sum the values [Result] if there are any rows of null values in the other field [Qual]. Example: 'Result' 'Qual' 'Lab_id' 1 1111 2 J 1111 3 1111 4 2222 ---------------------------- 4 1111 ('Result' being equal to four because the 'Result' "1" and "3" both have 'Qual' as "null" and the same 'Lab_id' as "1111"). The [Lab_id] is the field I want the query to sort the data, and calculation, by. Is there any way to do this without specifying specific values in the statement, but rather all values that are the same? -- Barry Guidry "Duane Hookom" wrote: I think you need to provide some context and sample data as well as desired results. Sum and Max work against a group of records while "another field is null" seems to work against just a single record. -- Duane Hookom Microsoft Access MVP "Barry" wrote: I would like to make a query on a table on one field to 'sum' or 'max' the field based on 'if' another field is null or not. Any idea on how I would go about this? Please be generous as I am not an Access expert :-) -- Barry |
Thread Tools | |
Display Modes | |
|
|