How to approach area searching

[wisni1rr]

I am creating a database that has four unique numbers the designate an area. Each number will contain no more that 3 digits. These numbers are tied into each other. For Example, An area may be 74:6:2:3. Where,
74 = county
6 = town
2 = Subdivision
3 = Further division.

I am stummped on my development. My client would want to search multiple areas at a time. each number ties back to the preceeding number. The search could also just include the 1st and second number and use wildcards for the 3rd and 4th numbers.

If this is not clear, please let me know. I will try to clarify this a bit more.

[Rabbit]

An example would help to clarify what you're looking for.

[wisni1rr]

Thanks Rabbit.

Consider the following data. Dashes are used like a format mask of xxx-xxx-xxx-xxx.

74-6-2-2 = Sub A
74-6-2-3 Sub B
74-6-2 = Area A
74-6-3-2 = Sub A
74-6-3 = Area B
74-6 Region A
74 = County A

The code represents an area. The first number designates a county. The second number designates a Region. The third number designates an Area. The forth number represents a subdivision. In other words, all the numbers are dependent on the number prior to it. I wouldn't have a number unless the number to the left of it is filled first.

The client would need to search by either of the four grouped items.

[Rabbit]

And they're all separate fields? Or is it all in one field?

[wisni1rr]

That's what I am trying to determine. How should I set the field or fields ups so they can be searched appropriately. I'm designing a new mysql database to replace the current access solutions. In access it has been set as 4 different fields. I'm just not sure if having them all separate will be the best approach when considering how they should be searchable.

[Rabbit]

They should be separate fields. That is the best approach for any situation. Combining the fields would make searching more difficult.

[wisni1rr]

Thanks, Rabbit.

I suspected as much.

As far as a search clause, it would most likely include nested where statements?

Num4 = "x" where Num3 = "y" and Num2 = "z" and Num1 = "q"

Am I thinking around the right lines?

[Rabbit]

Your WHERE should be at the beginning but yeah, that's the basic idea. Also, in most databases, you have to use single quotes to designate a string. Access let's you get away with either, but not MySQL.

WHERE Num4 = 'x' and Num3 = 'y' and Num2 = 'z' and Num1 = 'q'

[wisni1rr]

Superb. Thanks again, Rabbit!