Awkward query

[Ahern]

I have the following table which consists of chemical data. The data consists of two fields, one of which is the value and the other referes to whether or not the concentration was below the limit of detection (see example). I need to calculate the min, maximum and average by year which is not a problem. My problem is that I must also be able to report whether the calculated value is either less than what is stated or equal to it. I have illustrated the logic of the problem below

Year LOD Concentration
2000 < 1
2000 5
2001 1.5
2001 5

So that in my query, the resultant table would look like this once the query has run. For 2001 both values are above the limit of detection and so the query is straightforward. In 2000, the LOD needs to be reported for both the minimum and the average but not the maximum.

Year LOD Min LOD Max LOD Average
2000 < 1 5 < 3
2001 1.5 5 3.25

I am relatively new to access and cannot see a way to produce this query with the Limits of detection which works properly. Can anyone suggest a way to do this.

 

[stardusty]

Have you tried a serial queries?

Certainly there is a more elegant answer to your quest, but work must be accomplished and none other have come to your aid.

Set up the first query to ask only for the "less than" values of the minimums from the table of chemical data.

Set up the second query to ask only for the "less than" values of the maximums from the table of chemical data.

Set up the third query to ask only for the "less than" values of the averages from the table of chemical data.

Set up the fourth query, this one likely for your use to be a make table query, to ask for the "Year" field from you table of chemical data, the "less than" field of query 1, the "less than" field of query 2, and the "less than" field of query 3. Join properties in query 4 should be "all records from table of chemical data and only those from Query ?".

Hope that's right for you need
Brent

 

[Ahern]

Thanks for that. I agree it's not particularly elegant but it works a treat which is what really matters.



Alan Hern.