Range (2.0)

Topic

New Reply

I need to group our customers by a territory via their zip code.
I have a customer table with their zip code in a seperate column. Its a text data type because of Canada and Europe.
I have the territory table with territory number, states and zip code range.
The zip code range came to me as 039-049 (Maine) and I put 03900 in one column (lower zip code) and 04999 in another column (upper zip code).
The range does vary by state and area. Eastern Penn is 16900 thru 19699.

I am a little (alot?) stumped about how to match the company name and territory.

I appreciate any suggestions.

Thanks,
Mark

Reply | Quote

Replies

How do you need to group the customers – in the customer table permanently, on an occasional basis in a query, or on a one-time basis? Your table design should have a Country name or code of some sort so you can block off places where US-style ZIP codes don’t get into the mix. If you need to be able to do joins for queries and such, you probably should introduce a region variable, as doing a join on a range of numbers is difficult if not impossible. Have you been given a table of ZIP code ranges and territories? There are some 43000 ZIP codes in the US and it’s territories, so trying to work at that level isn’t very practical.

Reply | Quote

As Wendell wrote, if you have zip codes for different countries in one column, you’ll need some kind of country or region code too, because the structure of the zip codes is very different.

If it were just for 5 digit USA zip codes, you could create one or two queries to match companies and territories. Assuming that the zip code ranges don’t overlap, you only need one of the columns in your territory table; I used the lower zip code. Of course, you must replace the table and field names by the names you use.

Method 1: uses DLast; not very efficient, but just one query (I don’t know if DLast existed in Access 2.0):

SELECT CompanyName, ZipCode, DLast(“Territory”,”tblTerritory”,”[LowerZipCode]<='" & [ZipCode] & "'") AS Territory
FROM tblCustomers;

Method 2: two separate queries:

First: a query to get the max zip code from the Territory table that is less than or equal to the customer's zip code.

SELECT CompanyName, ZipCode, Max(LowerZipCode) AS MaxZip
FROM tblCustomers, tblTerritory
WHERE LowerZipCode<=ZipCode
GROUP BY CompanyName, ZipCode;

Let's call this query qryMaxZip.

Second: a query that uses the first to retrieve the territory name:

SELECT CompanyName, Territory
FROM qryMaxZip INNER JOIN tblTerritory ON qryMaxZip.MaxZip = tblTerritory.LowerZipCode;

Reply | Quote

Thanks for your input.

It seems like the best way to do this is to do it manually.
The territories shouldn’t change that often, if at all.

Thanks again for your help and advice.

Mark

Reply | Quote