Range Select Query for Addresses

[pvsmith5]

I have a table of streets, such as Main Street. I want to build a lookup form such that when someone enters the address "123 Main" they will get a message saying they are in the Blue Zone. Main Street, for example, may have a range from 1 to 4000, and addresses from, for example, 1 to 150 would be in the Blue Zone, 150-234 would be in the Red Zone, etc.

My question: Do I have to build a lookup table with all of the numbers listed consecutively (1 Main Street=Blue Zone, 2 Main Street=Blue Zone" and assign the respective zone to them? Somebody might enter a street address to lookup that I'm not ready for, especially if they extend the street and increase the number range.

This database table has about 800 streets and separate street segments. It could be a nightmare if I had to assign a single number to every zone that might possible be looked up.

I would appreciate any thoughts any of you would have on how to build this application.

 

[BNPMike]

If you define a zone as Street=Street_A, Start_Number=300, End_Number=2000, Zone_Name=Magenta, then you can join using less-than, and greater-than. Thus you don't need look-up values for each individual number. In other words SQL permits joins apart from the equijoin you most commonly see.

 

[pvsmith5]

Thank you. I should have thought of that. It seemed too easy, I guess.