How can I write a query that selects the most conservative value

[mvital]

I have an exposure limit table and in the table there is a field for source and a field for the limit and limit type.

I want to compare a result for a particular constituent to the most conservative limit between three sources and each source has sever different limit types for example a short term exposure, long term exposure etc.

Any ideas? I just don't know where to start.

thanks in advance!

mvital

 

[kjv1611]

Can you clarify by "conservative value"?

And when you say exposure, are you speaking of something like film exposure, or do you mean something else, like being exposed on a database user form?

 

[mvital]

By the most conservative value I mean the lowest value. By exposure I mean a level of exposure of a person to let's say a type of radiation or contaminant.

By comparing the person's level of exposure to a constituent to an establish permissable exposure limit and this limit is the lowest based on my three sources I know that if the result doesn't exceed that number it will not exceed any of the other sources because it has been compared to the lowest acceptable exposure.

It's a little confusing. I hope that clarifies it.

thanks for your reply!

 

[lespaul]

can you give us a "look" at your table? The field names, some sample data and expected results?

thanks

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[mvital]

This is the exposure limit table:

analyte exposure limits limit_type source units
antimony 2.5 ceiling ACGIH
antimony 50 ceiling OSHA
antimony 0.5 rel NIOSH
antimony 50 ceiling NIOSH
antimony 0.5 tlv ACGIH

ANALYTE RESULTS_MG/M3
ANTIMONY 0.0017
ANTIMONY 0.0011
ANTIMONY 0.00091
ANTIMONY 0.00079
ANTIMONY 0.0079
ANTIMONY 0.0012
ANTIMONY 0.0013
ANTIMONY 0.00091
ANTIMONY 0.0011
ANTIMONY 0.00091

I want to compare the results above the exposure limits in the first table to the lowest exposure limit. Which would be 0.5 and if any exceed mark them with a yes in another field. The lowest limit can be in any of the sources and any of the limit types.

thanks!

MV

 

[kjv1611]

Ok, that does help some, and as lespaul stated, some sample data would probably also help... If I get time, later on, I'll take a look at this a little more.

But, you may want to look at the MIN() function, just off the top of my head. That will pull the minimum value or least value in a particular field.

So something like:
intMyint = Min(Table.FieldName)

But, that will need some more stuff to be correct, I think, was just typing it down real quick.