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