Results 1 to 4 of 4

Thread: Range (2.0)

  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range (2.0)

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Range (2.0)

    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.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range (2.0)

    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;

  4. #4
    Star Lounger
    Join Date
    May 2001
    Location
    New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range (2.0)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •