Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lost on new territory (Access2002)

    Hello there,
    I'm quite fluent with Word and Excel, but new to Access and in need of help, if anybody would be willing to kindly offer it. I am working for a company that has showrooms in different states. We send out catalogues to customers,enclosing a letter referring them to the nearest showroom in the area. What I want Access to do is to automatically assign a customer to a showroom based on the address information I enter. I have Customer table with all the address information and a "Showroom Assigned" fields and a Showroom table listing all our showrooms. How can I get my data entry form based on the Customer table evaluate the information I have entered in the "State" and "Zip" field and fill in the "Showroom Assigned" field based on these entries? An example: we have showrooms in New York, Florida, Dallas and Houston. Should I have a customer who lives in New York, New Jersey, or Connecticut, I want them to be assigned to the New York showroom. Should a customer live in Florida or Alabama, I want them to be assigned to the Florida showroom. Now, the story becomes more complicated as I get a customer living in the state of Texas, as this state is split between the Dallas and Houston showrooms. If the first three numbers of customer's zip are 710, 712, 713 or 745, they should be assigned to Dallas showroom; otherwise, the rest of Texas customers should be assigned to Houston (of course, it's even more complicated, but that's the essence of my problem). If I still haven't lost you and you know how to accomplish the task (which probably is not all that difficult if you know what you're doing [img]/forums/images/smilies/smile.gif[/img] - -it's just me who's clueless), your help would be greatly appreciated.
    Thanks a bunch,
    Clueless, Curious and Ambitious [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    New Lounger
    Join Date
    May 2002
    Location
    Tasmania, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost on new territory (Access2002)

    Hi,
    You could try making a second table made up of a zip code field and a corresponding showroom field. (I would stick to just using zip codes as any other info like 'state' will just be secondary information.)Remove the 'showroom assigned' field from your customer table. Now, make a query using the customer table and the zip code table with relationship between the zip code fields in either table. If you look at this query in datasheet view you should see the data you want.

    BTW, down here in Oz, we can download a file of towns and cities and corresponding Post Codes from the Post Office web, site which can be imported into access. I would be very surprised if you can't do the same in the U.S.

    Hope this is helpful,

    Ryan

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lost on new territory (Access2002)

    You might want to include City and Zip. Zip codes don't cross state lines, but there are zip codes that are shared by more than one city and buildings and PO boxes with their own zip codes, not to mention those for military bases, etc.
    Charlotte

Posting Permissions

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