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
  #21  
Old December 21st, 2006, 12:40 PM posted to microsoft.public.access.queries
Jamie Collins
external usenet poster
 
Posts: 1,705
Default IIf statement


Barry wrote:

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]

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


Here's my latest attempt:

Create the test table:

CREATE TABLE Xylenes (
NAME VARCHAR(12) NOT NULL,
[DATE] DATETIME NOT NULL,
LAB_ID CHAR(6) NOT NULL,
ANALYTE VARCHAR(15) NOT NULL,
[RESULT] DECIMAL(17, 2) NOT NULL,
QUAL CHAR(2)
)
;

Create test data:

INSERT INTO Xylenes (
NAME, [DATE], LAB_ID, ANALYTE, [RESULT], QUAL)
VALUES ('RW-6', #2006-02-24 00:00:00#, '711721',
'o-Xylene', 0.4, 'ND')
;
INSERT INTO Xylenes (
NAME, [DATE], LAB_ID, ANALYTE, [RESULT], QUAL)
SELECT DT1.NAME, DT1.[DATE], DT1.LAB_ID,
DT1.ANALYTE, DT1.[RESULT], DT1.QUAL
FROM (
SELECT 'RW-5' AS NAME, #2006-02-24 00:00:00# AS [DATE],
'711722' AS LAB_ID, 'o-Xylene' AS ANALYTE,
0.4 AS [RESULT], 'ND' AS QUAL
FROM Xylenes
UNION ALL
SELECT 'RW-1', #2006-02-24 00:00:00#, '711723',
'm+p-Xylene', 2800, NULL
FROM Xylenes
UNION ALL
SELECT 'RW-1', #2006-02-24 00:00:00#, '711723',
'o-Xylene', 1900, NULL
FROM Xylenes
UNION ALL
SELECT 'Duplicate', #2006-02-24 00:00:00#, '711724',
'm+p-Xylene', 2500, NULL
FROM Xylenes
UNION ALL
SELECT 'Duplicate', #2006-02-24 00:00:00#, '711724',
'o-Xylene', 1700, NULL
FROM Xylenes
) AS DT1
;

The proposed solution:

SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(X1.[RESULT]) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(X1.[RESULT])
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1
;

Jamie.

--

  #22  
Old December 21st, 2006, 01:23 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default IIf statement

I got the SUM expression wrong. It should read
SUM(IIF(Qual is Null,Result, Null))
or
SUM(IIF(Qual is Not Null,Null, Result))


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,Result, Null))
, Max(IIF(Qual is Null, Null,Result))) as CalcResult
FROM TheTable as T1
GROUP BY T1.LAB_ID


"Barry" wrote in message
...
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











  #23  
Old December 21st, 2006, 01:50 PM posted to microsoft.public.access.queries
Jamie Collins
external usenet poster
 
Posts: 1,705
Default IIf statement


Jamie Collins wrote:

The proposed solution snipped


After peeking at John Spencer's proposal:

SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Jamie.

--

  #24  
Old December 21st, 2006, 03:18 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default IIf statement

Jamie that was very close to doing the trick. I have adapted it a bit to
achieve even a closer result (I don't know why) but what I have changed it to
is listed below (and below that is the results yielded). The only problem
with it now is it excluded one max value of "0.4" [Result] from the [Lab_id]
"711722". That seems strange to me because it did max the other similar row
of data, value "0.4" [Result] from [Lab_id] "711721". Thank you very much,
Jamie, and I would greatly appreciate it if you have any idea why the one row
of data may have been excluded.

SELECT X1.LAB_ID, 'max_of' AS aggregate_type,
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'sum_of',
SUM(IIF(X1.QUAL IS NOT NULL, NOT NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NOT NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Results:
LAB_ID aggregate_type aggregate_value
711721 max_of 0.4
711723 sum_of 4700
711723 max_of
711724 sum_of 4200
711724 max_of
--
Barry Guidry


"Jamie Collins" wrote:


Jamie Collins wrote:

The proposed solution snipped


After peeking at John Spencer's proposal:

SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Jamie.

--


  #25  
Old December 21st, 2006, 03:37 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default IIf statement

Sorry, after analyzing the results a bit more I realized that it is also
prioritizing the results with a not null [Qual], rather than the opposite. I
realized this after adding another row of null [Qual] data with same [Lab_id]
as a row with a not null [Qual]. And, it seems to be excluding the row of
data that contained only one row of data in a select single [Lab_id] group.
--
Barry Guidry


"Jamie Collins" wrote:


Jamie Collins wrote:

The proposed solution snipped


After peeking at John Spencer's proposal:

SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Jamie.

--


  #26  
Old December 21st, 2006, 03:46 PM posted to microsoft.public.access.queries
Jamie Collins
external usenet poster
 
Posts: 1,705
Default IIf statement


Jamie Collins wrote:

After peeking at John Spencer's proposal snipped


And after a peek at John's revised proposal:

SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(IIF(X1.QUAL IS NULL, X1.[RESULT], NULL))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Jamie.

--

  #27  
Old December 21st, 2006, 03:52 PM posted to microsoft.public.access.queries
Jamie Collins
external usenet poster
 
Posts: 1,705
Default IIf statement


Barry wrote:
Sorry, after analyzing the results a bit more I realized that it is also
prioritizing the results with a not null [Qual], rather than the opposite. I
realized this after adding another row of null [Qual] data with same [Lab_id]
as a row with a not null [Qual]. And, it seems to be excluding the row of
data that contained only one row of data in a select single [Lab_id] group.


Barry, If you are going to change the test data set (e.g. for the
better) please post it (rather than describe it) along with the
expected results. I took the time to post SQL DDL code to create the
test table and the test data using INSERT statements; it would be nice
if you could make similar efforts to keep us up to speed. I suspect I'm
not the only one finding the changing spec/data situation a little
frustrating, albeit a good challenge g.

Jamie.

--

  #28  
Old December 21st, 2006, 04:05 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default IIf statement

I have gotten even closer to my end result by adapting the SQL expression to
this:

SELECT X1.LAB_ID, 'max_of' AS aggregate_type,
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'sum_of',
SUM(IIF(X1.QUAL IS NOT NULL, NOT NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NOT NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

by reversing the order of the "SUM" and "MAX" functions, as well as the
"WHERE/WHERE NOT EXISTS", to yield these results:

LAB_ID aggregate_type aggregate_value
711721 sum_of 2
711722 sum_of
711722 max_of 0.4
711723 sum_of 4700

from this set of test data (added a couple more rows to original dataset):

SAMPLE DATE LAB_ID ANALYTE RESULT QUAL
RW-6 2/24/2006 711721 o-Xylene 0.4 ND
RW-6 2/24/2006 711721 o-Xylene 2
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
RW-1 2/24/2006 711723 m+p-Xylene 1 ND
Duplicate 2/24/2006 711724 m+p-Xylene 2500
Duplicate 2/24/2006 711724 o-Xylene 1700

So, the SQL is accurately selecting and sums the results with null [Qual]'s
over those with not null [Qual]'s (very good).
The only issue that remains is that it did not include a SUM of the two rows
of data with the [Lab_id] = "711724"; it appears for some reason that it was
due to both rows of data having null [Qual]'s. Maybe because it is only
comparing null/not null [Qual]'s and not additionally only summing the
results of data with all null [Qual]'s in the same group [Lab_id], right??
--
Barry Guidry


"Jamie Collins" wrote:


Jamie Collins wrote:

The proposed solution snipped


After peeking at John Spencer's proposal:

SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Jamie.

--


  #29  
Old December 21st, 2006, 04:22 PM posted to microsoft.public.access.queries
BARRY
external usenet poster
 
Posts: 383
Default IIf statement

I am sorry to be such a nuisance on this topic, but I feel that we are very
close to solving this issue. This is a common query of data in the
environmental science field that I believe many can benefit from once
complete, including other Access techies possibly.
--
Barry Guidry


"Barry" wrote:

I have gotten even closer to my end result by adapting the SQL expression to
this:

SELECT X1.LAB_ID, 'max_of' AS aggregate_type,
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'sum_of',
SUM(IIF(X1.QUAL IS NOT NULL, NOT NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NOT NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

by reversing the order of the "SUM" and "MAX" functions, as well as the
"WHERE/WHERE NOT EXISTS", to yield these results:

LAB_ID aggregate_type aggregate_value
711721 sum_of 2
711722 sum_of
711722 max_of 0.4
711723 sum_of 4700

from this set of test data (added a couple more rows to original dataset):

SAMPLE DATE LAB_ID ANALYTE RESULT QUAL
RW-6 2/24/2006 711721 o-Xylene 0.4 ND
RW-6 2/24/2006 711721 o-Xylene 2
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
RW-1 2/24/2006 711723 m+p-Xylene 1 ND
Duplicate 2/24/2006 711724 m+p-Xylene 2500
Duplicate 2/24/2006 711724 o-Xylene 1700

So, the SQL is accurately selecting and sums the results with null [Qual]'s
over those with not null [Qual]'s (very good).
The only issue that remains is that it did not include a SUM of the two rows
of data with the [Lab_id] = "711724"; it appears for some reason that it was
due to both rows of data having null [Qual]'s. Maybe because it is only
comparing null/not null [Qual]'s and not additionally only summing the
results of data with all null [Qual]'s in the same group [Lab_id], right??
--
Barry Guidry


"Jamie Collins" wrote:


Jamie Collins wrote:

The proposed solution snipped


After peeking at John Spencer's proposal:

SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Jamie.

--


  #30  
Old December 21st, 2006, 05:01 PM posted to microsoft.public.access.queries
Jamie Collins
external usenet poster
 
Posts: 1,705
Default IIf statement


Barry wrote:

I have gotten even closer to my end result by adapting the SQL expression to
this:

SELECT X1.LAB_ID, 'max_of' AS aggregate_type,
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'sum_of',
SUM(IIF(X1.QUAL IS NOT NULL, NOT NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NOT NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

by reversing the order of the "SUM" and "MAX" functions, as well as the
"WHERE/WHERE NOT EXISTS", to yield these results:

LAB_ID aggregate_type aggregate_value
711721 sum_of 2
711722 sum_of
711722 max_of 0.4
711723 sum_of 4700

from this set of test data (added a couple more rows to original dataset):

SAMPLE DATE LAB_ID ANALYTE RESULT QUAL
RW-6 2/24/2006 711721 o-Xylene 0.4 ND
RW-6 2/24/2006 711721 o-Xylene 2
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
RW-1 2/24/2006 711723 m+p-Xylene 1 ND
Duplicate 2/24/2006 711724 m+p-Xylene 2500
Duplicate 2/24/2006 711724 o-Xylene 1700

So, the SQL is accurately selecting and sums the results with null [Qual]'s
over those with not null [Qual]'s (very good).
The only issue that remains is that it did not include a SUM of the two rows
of data with the [Lab_id] = "711724"; it appears for some reason that it was
due to both rows of data having null [Qual]'s. Maybe because it is only
comparing null/not null [Qual]'s and not additionally only summing the
results of data with all null [Qual]'s in the same group [Lab_id], right??


This?

SELECT X1.LAB_ID, 'max_of' AS aggregate_type,
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'sum_of',
SUM(IIF(X1.QUAL IS NULL, X1.[RESULT], NULL))
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Jamie.

--

 




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 01: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.