If statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 :-)
 
Use the Immediate If function:

IIf([FieldName]<expression>, TruePart,FalsePart)

and the IsNull() function:

IIf(IsNull([MyField), Sum([MyOtherField], Max([MyOtherField])
 
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 :-)
Click to expand...

Sounds like you need a search condition (WHERE clause) e.g.

SELECT MAX(column1)
FROM MyTable
WHERE test_column IS NOT NULL;

but I suppose you could want some inline logic e.g.

SELECT SUM(IIF(test_column IS NULL, column2, column1))
FROM MyTable;

Jamie.

--
 
Barry said:
Jamie, is it possible to use one of your expressions that includes both the
MAX and SUM function in one field?
Click to expand...

Probably but I must admit I've no idea what you are trying to achieve
e.g.

SELECT T1.MyField, IIF(T1.MyField IS NULL, DT1.sum_of, DT1.max_of)
FROM
(
SELECT SUM(T2.MyOtherField) AS sum_of,
MAX(T2.MyOtherField) AS max_of
FROM MyTable AS T2
) AS DT1, MyTable AS T1;

??

Jamie.

--
 
What I would like to do is have it return either/or a sum of values from
[Result] field, IF any one row of data contains a null value in [Qual] field,
or else the maximum value of any one row of data in [Result] IF all values in
[Qual] are not null.
Click to expand...

Is this any closer?

SELECT IIF(EXISTS (
SELECT *
FROM MyTable AS T2
WHERE T2.Qual IS NULL
), DT1.sum_of, DT1.max_of) AS sum_or_max_of
FROM
(
SELECT MAX([Result]) AS max_of, SUM([Result]) AS sum_of
FROM MyTable
) AS DT1;

Jamie.

--
 
Barry said:
Jamie, I tried it but it returned all data of [Result] summed together
without regards to [Qual] being null or not.
Click to expand...

That is not correct.

Here is my test case:

CREATE TABLE MyTable (
[Result] INTEGER,
Qual INTEGER)
;
INSERT INTO MyTable ([Result], Qual) VALUES (5, 1)
;
INSERT INTO MyTable ([Result], Qual) VALUES (6, 1)
;
INSERT INTO MyTable ([Result], Qual) VALUES (7, 1)
;

SELECT MAX([Result]) AS max_of,
SUM([Result]) AS sum_of
FROM MyTable;

returns max_of = 7, sum_of = 18

Your statement, "[your query] returned all data of [Result] summed
together without regards to [Qual] being null or not" is a false
statement, which can be demonstrated as follows:

UPDATE MyTable SET Qual = NULL
;
SELECT IIF(EXISTS (
SELECT *
FROM MyTable AS T2
WHERE T2.Qual IS NULL
), DT1.sum_of, DT1.max_of) AS sum_or_max_of
FROM
(
SELECT MAX([Result]) AS max_of, SUM([Result]) AS sum_of
FROM MyTable
) AS DT1
;

returns sum_or_max = 18

UPDATE MyTable SET Qual = 1
;
SELECT IIF(EXISTS (
SELECT *
FROM MyTable AS T2
WHERE T2.Qual IS NULL
), DT1.sum_of, DT1.max_of) AS sum_or_max_of
FROM
(
SELECT MAX([Result]) AS max_of, SUM([Result]) AS sum_of
FROM MyTable
) AS DT1
;

returns sum_or_max = 7

The result changes based on the presence of a NULL value for Qual,
making your statement false.

My interpretation of your spec is:

If any one row of data contains a null value in [Qual] field then
return a sum of all values from [Result] field
else
return the maximum value of [Result]

I am confident my interpretation is correct. Did you word your spec
incorrectly said:
I have accidentally posted the same question in another thread (through a
different link to the newsgroup on Microsoft's website).
Click to expand...

You seem to have posted test data to the other thread so I'll take a
look now.

Jamie.

--
 
Sorry, I've been on vacation with spotty Internet.

You need to give the column an alias like:

Expr1: IIf(IsNull([MyField), Sum([MyOtherField], Max([MyOtherField])

--
Arvin Meyer, MCP, MVP
Microsoft Access
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Do not use an aggregate query, since the Sum and Max functions are part of
the expression.
Barry said:
Arvin, I tried this but it returned the infamous "You tried to execute a
query that does not include the specified expression...as part of an
aggregate function."

Jamie, is it possible to use one of your expressions that includes both
the
MAX and SUM function in one field?

Thanks
--
Barry


Arvin Meyer said:
Use the Immediate If function:

IIf([FieldName]<expression>, TruePart,FalsePart)

and the IsNull() function:

IIf(IsNull([MyField), Sum([MyOtherField], Max([MyOtherField])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Barry said:
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 :-)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top