By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,067 Members | 1,818 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,067 IT Pros & Developers. It's quick & easy.

How to approach area searching

P: 78
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.
Jan 11 '12 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,359
An example would help to clarify what you're looking for.
Jan 11 '12 #2

P: 78
Thanks Rabbit.

Consider the following data. Dashes are used like a format mask of xxx-xxx-xxx-xxx.
Expand|Select|Wrap|Line Numbers
  1. 74-6-2-2 = Sub A
  2. 74-6-2-3 Sub B
  3. 74-6-2 = Area A
  4. 74-6-3-2 = Sub A
  5. 74-6-3 = Area B
  6. 74-6 Region A
  7. 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.
Jan 12 '12 #3

Rabbit
Expert Mod 10K+
P: 12,359
And they're all separate fields? Or is it all in one field?
Jan 12 '12 #4

P: 78
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.
Jan 12 '12 #5

Rabbit
Expert Mod 10K+
P: 12,359
They should be separate fields. That is the best approach for any situation. Combining the fields would make searching more difficult.
Jan 12 '12 #6

P: 78
Thanks, Rabbit.

I suspected as much.

As far as a search clause, it would most likely include nested where statements?
Expand|Select|Wrap|Line Numbers
  1. Num4 = "x" where Num3 = "y" and Num2 = "z" and Num1 = "q"
Am I thinking around the right lines?
Jan 12 '12 #7

Rabbit
Expert Mod 10K+
P: 12,359
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.
Expand|Select|Wrap|Line Numbers
  1. WHERE Num4 = 'x' and Num3 = 'y' and Num2 = 'z' and Num1 = 'q'
Jan 12 '12 #8

P: 78
Superb. Thanks again, Rabbit!
Jan 12 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.