searching records that use a range for Values

[blueman]

I am trying to figure out the best way to set up tables that have
value ranges. This is to be used for entering Plant Information.

Heights,etc are typically expressed in ranges, i.e. 10-12', 12-14',
14-16' etc. Sometimes it might be necessary to create an entry where
heights are 10-16'. I thought about using 2 fields for each record;
"min height" & "max height". I'm concerned about how I would
eventually construct a query to search for ranges if this is the
proper thing to do. I am not a programmer (I don't know VBA) and do
this in my spare time for business. I'm wondering if there is a
simpler way to set the table up or maybe if this is still the best way
to go about it. If it is, how would I set up the query to search for
say >than 14' trees using a query that uses the underlying table with
the Min Height & Max Height fields


Thanks

 

[Allen Browne]

One of the basic rules of normalization is to make sure the data is atomic,
so your idea of using 2 fields to store the min and max values is *much*
better than storing 2 pieces of text in one field, (such as 12-14.)

The next question is whether these numbers represent discrete, contiguous
ranges:
a) Would it be an error to have overlapping values (e.g. 10 - 14, 12 - 16)?
b) Would it be an error to have missing values (e.g. 10 - 12, 14-16, but 13
is not defined anywhere so the database can't handle it)?

If the answer to both questions is Yes, then you should store only the Min
value, not the max, since that is determined by examining the next record.
Designing the database like this does not need VBA, but does need a little
understanding of queries. Tom Ellision explains how in this article:
Lookup a value in a range
at:
http://allenbrowne.com/ser-58.html

 

[blueman]

Great answer Allen. Well explained!
It was a yes to both.

Now that I've looked at this closer, it seems like in this case,
however, I might want to, for example, use the following in a query
for searching for range of trees with height of 20-25'"

Under the "Min Height" field put: >=20
Under the "Max Height" field put: <=25

This should return all records with values between those parameters.

I guess I should create some type of input form that allows the user
to put in the height parameters. When the user hits the command button
it would run the query using those parameters and inputting the >= or
<= in the proper fields at the same time.

Does this seem to make sense?

Thanks

Mike

 

[Allen Browne]

Yes: if you are giving the user the chance to enter a range, then you need
to identify overlapping ranges.

The overlaps occur where:
- Max user value > min range in table, AND
- Max range in table > min user value.

You can diagram that to get the picture of where 2 things overlap:
A: min------------max
B: min-------max
etc