If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
IIf statement
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 |
#2
|
|||
|
|||
IIf statement
Type the expression you want in the Field row in query design, e.g.:
IIf([CreditDate] Is Not Null, [CreditAmount], 0) You can then Sum or choose the Max of this value in the Total row. (Depress the Total icon on the toolbar if you don't see the Total row in query design.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry" wrote in message ... 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 |
#3
|
|||
|
|||
IIf statement
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 |
#4
|
|||
|
|||
IIf statement
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 |
#5
|
|||
|
|||
IIf statement
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 |
#6
|
|||
|
|||
IIf statement
Yes, thanks to Allen Browne, his suggestion, IIf([CreditDate] Is Not Null,
[CreditAmount], 0) You can then Sum or choose the Max of this value in the Total row, does work to do one or the other (either sum or max). But, what I am trying to do is to do a combination of the two (both sum or max) in one expression, based on whether or not the other field contains a null value or not. -- 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 |
#7
|
|||
|
|||
IIf statement
I believe that the problem may be that access may try to evaluate both the
true and the false options before deciding which one is wanted. "Barry" wrote in message ... Yes, thanks to Allen Browne, his suggestion, IIf([CreditDate] Is Not Null, [CreditAmount], 0) You can then Sum or choose the Max of this value in the Total row, does work to do one or the other (either sum or max). But, what I am trying to do is to do a combination of the two (both sum or max) in one expression, based on whether or not the other field contains a null value or not. -- 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 |
#8
|
|||
|
|||
IIf statement
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 |
#9
|
|||
|
|||
IIf statement
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 | |
|
|