A Question on Subqueries

[Tomas C]

Howdy Folks,

I have an involved request that is proving difficult for me, although I
have a few ideas. Here is my situation: I have a table (that resulted
from several previous queries) containing groundwater analytical
chemistry data over the past several years. It holds almost 160,000
records and has the following structure:

[tbl_minmax04_paredown]

sys_sample_code
sys_loc_code
sample_type_code
sample_date
chemical_name
calc_result
reporting_detection_limit
result_unit
detect_flag
year
quarter
historical_yn
interp_qual
comment

For the purposes of clarification, sys_loc_code is the well that was
sampled, calc_result contains the result of the analysis (if nothing was
detected for a particular compound, the detection limit is used),
detect_flag either contains Y or N, historical_yn either contains Y or
N, and interp_qual is a text value that is occasionally used to denote
something special about a given result.

With that out of the way, here's what I'd like to do:

Pull out the maximum and minimum values for a each compound at each
well. That's not too tricky, but the next part is where I start to struggle.

Since my result value field always contains a number, it is easy to
calculate a max or a min. However, the number alone is not the full
story. I would like to know if that number represents a detection, or a
non-detection. This is where I think a subquery would do the trick, but
I am struggling with how to do it.

I would like a table that shows something like:

[table_name]
sys_loc_code
chemical_name
max_result
max_detect_flag
min_result
min_detect_flag
result_unit
historical_yn

Possible sticking points include when a detected and non-detected value
are identical. In the case of minima, the non-detected value should be
the one displayed. In the case of maxima, the detected should be
displayed. At this point, my head starts to hurt.

Can all this be done with standard Access 2003/Queries/Subqueries, or do
I need to dive into something like VBA?

If I could get this far with queries, I would be most happy. There is
one last icing on the cake request, although it is far less important.

EXTRA SPECIAL BONUS

If a data point is qualified, that is if interp_qual is not null for a
given max or min value, I would like to have that returned in a separate
field (actually two separate fields, one for max and one for min). In
the event that there are two identical values, one qualified and one
without any qualifiers, no qualifier would be returned.

However, the qualifiers are far less important than just the max and min.

As always, my sincere thanks to anyone who has read this far, let alone
volunteers some advice.

 

[John Nurick]

Hi Tomas,

With luck these will get you started. I'd work separately on the
maxima and minima and then union them before doing the final crosstab.

This one seems to do the basic 'max' job but doesn't discriminate
between detections non-detections:

SELECT A.sys_loc_code, A.chemical_name, A.calc_result, A.detect_flag
FROM tbl_minmax04_paredown AS A
WHERE A.calc_result = (
SELECT MAX(B.calc_result) FROM tbl_minmax04_paredown AS B
WHERE B.sys_loc_code = A.sys_loc_code
AND B.chemical_name = A.chemical_name
)
ORDER BY A.sys_loc_code, A.chemical_name
;

This converts the detect_flag into a small numeric value to
discriminate between detectoins and non-detections:

SELECT A.sys_loc_code, A.chemical_name, A.calc_result, A.detect_flag
FROM tbl_minmax04_paredown AS A
WHERE A.calc_result + IIF(A.detect_flag = "Y", 1e-6, 0) = (
SELECT MAX(B.calc_result + IIF(B.detect_flag = "Y", 1e-6, 0))
FROM tbl_minmax04_paredown AS B
WHERE B.sys_loc_code = A.sys_loc_code
AND B.chemical_name = A.chemical_name
)
ORDER BY A.sys_loc_code, A.chemical_name
;

You don't specify which record to choose where there is more than one
reading with the same locatoin, chemical, result and detect_flag
value, so there'll be a bit more complication there.

Howdy Folks,

I have an involved request that is proving difficult for me, although I
have a few ideas. Here is my situation: I have a table (that resulted
from several previous queries) containing groundwater analytical
chemistry data over the past several years. It holds almost 160,000
records and has the following structure:

[tbl_minmax04_paredown]

sys_sample_code
sys_loc_code
sample_type_code
sample_date
chemical_name
calc_result
reporting_detection_limit
result_unit
detect_flag
year
quarter
historical_yn
interp_qual
comment

For the purposes of clarification, sys_loc_code is the well that was
sampled, calc_result contains the result of the analysis (if nothing was
detected for a particular compound, the detection limit is used),
detect_flag either contains Y or N, historical_yn either contains Y or
N, and interp_qual is a text value that is occasionally used to denote
something special about a given result.

With that out of the way, here's what I'd like to do:

Pull out the maximum and minimum values for a each compound at each
well. That's not too tricky, but the next part is where I start to struggle.

Since my result value field always contains a number, it is easy to
calculate a max or a min. However, the number alone is not the full
story. I would like to know if that number represents a detection, or a
non-detection. This is where I think a subquery would do the trick, but
I am struggling with how to do it.

I would like a table that shows something like:

[table_name]
sys_loc_code
chemical_name
max_result
max_detect_flag
min_result
min_detect_flag
result_unit
historical_yn

Possible sticking points include when a detected and non-detected value
are identical. In the case of minima, the non-detected value should be
the one displayed. In the case of maxima, the detected should be
displayed. At this point, my head starts to hurt.

Can all this be done with standard Access 2003/Queries/Subqueries, or do
I need to dive into something like VBA?

If I could get this far with queries, I would be most happy. There is
one last icing on the cake request, although it is far less important.

EXTRA SPECIAL BONUS

If a data point is qualified, that is if interp_qual is not null for a
given max or min value, I would like to have that returned in a separate
field (actually two separate fields, one for max and one for min). In
the event that there are two identical values, one qualified and one
without any qualifiers, no qualifier would be returned.

However, the qualifiers are far less important than just the max and min.

As always, my sincere thanks to anyone who has read this far, let alone
volunteers some advice.

 

[Thomas Campitelli]

Dear John,

Many thanks for your response. I will get to work on this and let you
know how things go.

Tom

Hi Tomas,

With luck these will get you started. I'd work separately on the
maxima and minima and then union them before doing the final crosstab.

This one seems to do the basic 'max' job but doesn't discriminate
between detections non-detections:

SELECT A.sys_loc_code, A.chemical_name, A.calc_result, A.detect_flag
FROM tbl_minmax04_paredown AS A
WHERE A.calc_result = (
SELECT MAX(B.calc_result) FROM tbl_minmax04_paredown AS B
WHERE B.sys_loc_code = A.sys_loc_code
AND B.chemical_name = A.chemical_name
)
ORDER BY A.sys_loc_code, A.chemical_name
;

This converts the detect_flag into a small numeric value to
discriminate between detectoins and non-detections:

SELECT A.sys_loc_code, A.chemical_name, A.calc_result, A.detect_flag
FROM tbl_minmax04_paredown AS A
WHERE A.calc_result + IIF(A.detect_flag = "Y", 1e-6, 0) = (
SELECT MAX(B.calc_result + IIF(B.detect_flag = "Y", 1e-6, 0))
FROM tbl_minmax04_paredown AS B
WHERE B.sys_loc_code = A.sys_loc_code
AND B.chemical_name = A.chemical_name
)
ORDER BY A.sys_loc_code, A.chemical_name
;

You don't specify which record to choose where there is more than one
reading with the same locatoin, chemical, result and detect_flag
value, so there'll be a bit more complication there.

Howdy Folks,

I have an involved request that is proving difficult for me, although I
have a few ideas. Here is my situation: I have a table (that resulted
from several previous queries) containing groundwater analytical
chemistry data over the past several years. It holds almost 160,000
records and has the following structure:

[tbl_minmax04_paredown]

sys_sample_code
sys_loc_code
sample_type_code
sample_date
chemical_name
calc_result
reporting_detection_limit
result_unit
detect_flag
year
quarter
historical_yn
interp_qual
comment

For the purposes of clarification, sys_loc_code is the well that was
sampled, calc_result contains the result of the analysis (if nothing was
detected for a particular compound, the detection limit is used),
detect_flag either contains Y or N, historical_yn either contains Y or
N, and interp_qual is a text value that is occasionally used to denote
something special about a given result.

With that out of the way, here's what I'd like to do:

Pull out the maximum and minimum values for a each compound at each
well. That's not too tricky, but the next part is where I start to struggle.

Since my result value field always contains a number, it is easy to
calculate a max or a min. However, the number alone is not the full
story. I would like to know if that number represents a detection, or a
non-detection. This is where I think a subquery would do the trick, but
I am struggling with how to do it.

I would like a table that shows something like:

[table_name]
sys_loc_code
chemical_name
max_result
max_detect_flag
min_result
min_detect_flag
result_unit
historical_yn

Possible sticking points include when a detected and non-detected value
are identical. In the case of minima, the non-detected value should be
the one displayed. In the case of maxima, the detected should be
displayed. At this point, my head starts to hurt.

Can all this be done with standard Access 2003/Queries/Subqueries, or do
I need to dive into something like VBA?

If I could get this far with queries, I would be most happy. There is
one last icing on the cake request, although it is far less important.

EXTRA SPECIAL BONUS

If a data point is qualified, that is if interp_qual is not null for a
given max or min value, I would like to have that returned in a separate
field (actually two separate fields, one for max and one for min). In
the event that there are two identical values, one qualified and one
without any qualifiers, no qualifier would be returned.

However, the qualifiers are far less important than just the max and min.

As always, my sincere thanks to anyone who has read this far, let alone
volunteers some advice.

 

[Guest]

But try doing it with make table queries and append queries
first, so that you can see what you are working with at each
stage.

(david)

Howdy Folks,

I have an involved request that is proving difficult for me, although I
have a few ideas. Here is my situation: I have a table (that resulted
from several previous queries) containing groundwater analytical
chemistry data over the past several years. It holds almost 160,000
records and has the following structure:

[tbl_minmax04_paredown]

sys_sample_code
sys_loc_code
sample_type_code
sample_date
chemical_name
calc_result
reporting_detection_limit
result_unit
detect_flag
year
quarter
historical_yn
interp_qual
comment

For the purposes of clarification, sys_loc_code is the well that was
sampled, calc_result contains the result of the analysis (if nothing was
detected for a particular compound, the detection limit is used),
detect_flag either contains Y or N, historical_yn either contains Y or
N, and interp_qual is a text value that is occasionally used to denote
something special about a given result.

With that out of the way, here's what I'd like to do:

Pull out the maximum and minimum values for a each compound at each
well. That's not too tricky, but the next part is where I start to struggle.

Since my result value field always contains a number, it is easy to
calculate a max or a min. However, the number alone is not the full
story. I would like to know if that number represents a detection, or a
non-detection. This is where I think a subquery would do the trick, but
I am struggling with how to do it.

I would like a table that shows something like:

[table_name]
sys_loc_code
chemical_name
max_result
max_detect_flag
min_result
min_detect_flag
result_unit
historical_yn

Possible sticking points include when a detected and non-detected value
are identical. In the case of minima, the non-detected value should be
the one displayed. In the case of maxima, the detected should be
displayed. At this point, my head starts to hurt.

Can all this be done with standard Access 2003/Queries/Subqueries, or do
I need to dive into something like VBA?

If I could get this far with queries, I would be most happy. There is
one last icing on the cake request, although it is far less important.

EXTRA SPECIAL BONUS

If a data point is qualified, that is if interp_qual is not null for a
given max or min value, I would like to have that returned in a separate
field (actually two separate fields, one for max and one for min). In
the event that there are two identical values, one qualified and one
without any qualifiers, no qualifier would be returned.

However, the qualifiers are far less important than just the max and min.

As always, my sincere thanks to anyone who has read this far, let alone
volunteers some advice.

 

[Tomas C]

Hey John,

Thank you for your contributions. Ultimately the SQL you provided ran
too slowly. I was able to break things up into multiple make-table
queries, delete queries, and update queries to meet my goals. Subqueries
are powerful, but I have a really hard time working through them. I just
don't know SQL well enough. Thank you again for your help.

Tom

Hi Tomas,

With luck these will get you started. I'd work separately on the
maxima and minima and then union them before doing the final crosstab.

This one seems to do the basic 'max' job but doesn't discriminate
between detections non-detections:

SELECT A.sys_loc_code, A.chemical_name, A.calc_result, A.detect_flag
FROM tbl_minmax04_paredown AS A
WHERE A.calc_result = (
SELECT MAX(B.calc_result) FROM tbl_minmax04_paredown AS B
WHERE B.sys_loc_code = A.sys_loc_code
AND B.chemical_name = A.chemical_name
)
ORDER BY A.sys_loc_code, A.chemical_name
;

This converts the detect_flag into a small numeric value to
discriminate between detectoins and non-detections:

SELECT A.sys_loc_code, A.chemical_name, A.calc_result, A.detect_flag
FROM tbl_minmax04_paredown AS A
WHERE A.calc_result + IIF(A.detect_flag = "Y", 1e-6, 0) = (
SELECT MAX(B.calc_result + IIF(B.detect_flag = "Y", 1e-6, 0))
FROM tbl_minmax04_paredown AS B
WHERE B.sys_loc_code = A.sys_loc_code
AND B.chemical_name = A.chemical_name
)
ORDER BY A.sys_loc_code, A.chemical_name
;

You don't specify which record to choose where there is more than one
reading with the same locatoin, chemical, result and detect_flag
value, so there'll be a bit more complication there.

Howdy Folks,

I have an involved request that is proving difficult for me, although I
have a few ideas. Here is my situation: I have a table (that resulted
from several previous queries) containing groundwater analytical
chemistry data over the past several years. It holds almost 160,000
records and has the following structure:

[tbl_minmax04_paredown]

sys_sample_code
sys_loc_code
sample_type_code
sample_date
chemical_name
calc_result
reporting_detection_limit
result_unit
detect_flag
year
quarter
historical_yn
interp_qual
comment

For the purposes of clarification, sys_loc_code is the well that was
sampled, calc_result contains the result of the analysis (if nothing was
detected for a particular compound, the detection limit is used),
detect_flag either contains Y or N, historical_yn either contains Y or
N, and interp_qual is a text value that is occasionally used to denote
something special about a given result.

With that out of the way, here's what I'd like to do:

Pull out the maximum and minimum values for a each compound at each
well. That's not too tricky, but the next part is where I start to struggle.

Since my result value field always contains a number, it is easy to
calculate a max or a min. However, the number alone is not the full
story. I would like to know if that number represents a detection, or a
non-detection. This is where I think a subquery would do the trick, but
I am struggling with how to do it.

I would like a table that shows something like:

[table_name]
sys_loc_code
chemical_name
max_result
max_detect_flag
min_result
min_detect_flag
result_unit
historical_yn

Possible sticking points include when a detected and non-detected value
are identical. In the case of minima, the non-detected value should be
the one displayed. In the case of maxima, the detected should be
displayed. At this point, my head starts to hurt.

Can all this be done with standard Access 2003/Queries/Subqueries, or do
I need to dive into something like VBA?

If I could get this far with queries, I would be most happy. There is
one last icing on the cake request, although it is far less important.

EXTRA SPECIAL BONUS

If a data point is qualified, that is if interp_qual is not null for a
given max or min value, I would like to have that returned in a separate
field (actually two separate fields, one for max and one for min). In
the event that there are two identical values, one qualified and one
without any qualifiers, no qualifier would be returned.

However, the qualifiers are far less important than just the max and min.

As always, my sincere thanks to anyone who has read this far, let alone
volunteers some advice.