Handling inequalities on records. Is there a standard way to do it?

[Javier Hernández]

Hi everyone. There are situations where a record may have a number value but rather than representing "equal to" it must state "less than" or "greater than". For example, in chemical analysis results below the specified limit of quantitation have to be declared as "less than" like "< 0,5 µg / L".

This poses a problem. Such inequality can't be expressed in a typical number field, but has to be considered in queries (for example, a query for iron results below 0,5 mg / L should also detect those expressed as "< 0,3 mg / L"). Our database currently stores results as strings so characters like < or > can be displayed. I dislike this because it messes with queries and requires string manipulations and conversions. Right now, I can think of creating a new field which stores the type of statement (<, > or =) so I can evaluate it and proceed.

I guess this is an issue that has already been dealt with before, so I want to avoid reinventing the wheel or even worse, creating an squared one without noticing. Do you have experience regarding this situation?

[orange]

Have you considered a lookup table or tables? I'm not sure how I'd handle any arithmetic, but I'm sure it can be done.

I see a table or tables for various ranges of units of acceptance.

ID    Hi_Lo_range

1     <0,5 mg/L and >0,3 mg/L
2     <0,3 mg/L and >0,1 mg/L
........

We don't know much about your data, nor the precision involved. If the range is important, then using adequate ids and ranges should work. You could have another field in the table numeric data type with a specific value. If you need multiple ranges, then multiple Acceptance tables with appropriate ranges may apply.

What exactly do you want to record.
eg 0,378 mg/L or it is a ID = 1 ???
Just a few thoughts for consideration.

[NTC]

symbols definitely cannot be entered into a number field and so you essentially have answered your own question - in that the symbol must go into a separate field. This should represent no obstacle for queries as you can use that symbol as part of the criteria
< <.5 in two fields ......... will return all records that have the < symbol and all that are .5 or less