Setting up tables with range Values for searching later

[plantfinder]

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, possibly I should post this in the queries forum.

Thanks

Mike

 

[dhookom]

You might find min and max values the easiest solution. You can add the value range table to a query and set the criteria under your [Height] field to something like:
>=[MinHeight] AND <[MaxHeight]

Duane
Hook'D on Access
MS Access MVP

 

[plantfinder]

Are you saying I should put this expression in the criteria cell under Min or Max Height field of the query? I don't have a "Height field" since I only have "Min Height" & "Max Height Fields". Also, let's say I wanted to search for > 14' plants, where would I enter the 14 into the expression. Thanks

 

[dhookom]

Perhaps you should re-state your issue. I assumed you had a numeric field that you wanted to group into ranges.

From re-reading your original posting, I now assume you have a table of plants (like flowers and shrubs). If I am correct, I would use two fields in your plant table to store the min and max heights. Then, if you are looking for a plant that might grow to 24" you could use a text box on a form for users to enter the height frmCriteria!txtHeight. Then in the query of the plant table, create a new column:
Field: forms!frCriteria!txtHeight
Criteria: Between [MinHeight] And [MaxHeight]



Duane
Hook'D on Access
MS Access MVP