Capturing Sales Territory Number Based on Zip Code

[leekirt]

I'd like to be able to set up the "Territory Number" field in my database in such a way that it looks at the zip code in the record and then goes out to my Sales Territory table and finds the correct territory. The way the Sales Territory table is set up is as follows....It contains a Territory number, a "startZip" (such as 00000) and an "endZip" (such as 02100). I'd like the calculation to find the correct territory by comparing the zip code and finding the correct zip code range. Does this make any sense? Does anyone know how I can do this?

[DesignX]

why dont you just use a relationship with a lokup field.

make a zipcode field in both the sales territory table and the other table and make them related andthen makethe territory number a lookup field

[leekirt]

I don't think that's possible, because the territory number depends upon a range of zip codes. For example, territory number 626 contains any zip code that falls between "00000" and "01000", territory number 603 contains any zip code that falls between "01500" and "01700". These are just two examples, but I have 152 possible zip code ranges that have been established in my Sales Territory table.

[aholtzapfel]

Is very poosible, in the look-up options you can have it look-up (relationship zipcode = SalesTerr::FirstZipcode) and if no match is found it can "lookup next lower value", the related table would only have to include the First zipcode for the territory, and will update any time a change in the zip code field is commited.

Another option is a more complex relationship (Relationship - Zipcode >= SalesTerr::FirstZipcode AND

Zipcode <= SalesTerr::LastZipcode).

using the second method is a little more veritile. (you can display the value from the related table without having to Look it up, as well as viewing anything else in that table, Like Contact info for that territory)

I hope I was clear enough. If not I will throw together a simple sample file.

[leekirt]

Thank you for the information. I think the second option is the one I need, because I might like to call up the Sales Rep Name, in addition to the territory number. I had put this off, because I figured if I got the territory number, I could use a lookup to get the Sales Rep Name. If it isn't too much to ask, I'd really appreciate a sample file. I've never done this before and, while I understand concepts, I'm struggling to figure out where things belong. If it's a pain, I understand and will plug away until I figure it out. Great help! Thanks again. :smile2:

[aholtzapfel]

Here is a sample of the 2nd option, It is very crude but should show you what you need.

Sample.zip.zip

[leekirt]

Thank you so much for doing this. It makes the whole thing so much clearer. I was so convinced I would need to use a calculation function, that I never saw the place where you can define the relationship. In fact, I kept wondering what everyone meant when they said this. I was also trying to create a relationship between the two territory fields, instead of the zip code fields. Bottom line...I would never have figured this out (I have so much to learn).