A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

IIf statement



 
 
Thread Tools Display Modes
  #1  
Old December 20th, 2006, 03:03 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default 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  
Old December 20th, 2006, 03:27 PM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old December 20th, 2006, 03:34 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old December 20th, 2006, 04:03 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default 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  
Old December 20th, 2006, 04:34 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old December 20th, 2006, 04:43 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default 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  
Old December 20th, 2006, 04:50 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default 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

  #8  
Old December 20th, 2006, 04:58 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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

  #9  
Old December 20th, 2006, 05:04 PM posted to microsoft.public.access.queries
David F Cox
external usenet poster
 
Posts: 493
Default 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




  #10  
Old December 20th, 2006, 05:16 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default IIf statement

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.