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.

--