International Addresses

[T'Kai]

I have been asked to create a database capable of handling both international and US addresses. Does anyone have any idea how to handle this efficiently? Keep in mind that letters, label, etc. will need to be produced. I was considering creating a 255 character text box and just let the user dump the entire address here. However, this does not help with filtering if a mailing needs to be send to only a specific state or country. Any suggestions would be appreciated.

 

[Tim Ferguson]

I have been asked to create a database capable of handling both
international and US addresses.

filtering if a mailing needs to be send to only a
specific state or country.

.... in between these two requirements you have a whole bunch of needs
assessing still to do. _Exactly_ how are you going to filter by a
particular state in countries that don't have states? What do you want to
about UK (contains four countries) or France and Germany (several regions)
and so on?

When you have all these answers (and a load more that I have not thought
of, and many, many more that you have not mentioned) then the necessary
design becomes obvious. Until then...

Hope that helps


Tim F

 

[Jay Vinton]

We have customers all over the world but the table design is standard

Address
Address
Address
Cit
Stat
Zi
Countr

The difference is in the UI. They can choose to disply different formats according to their local custom. To do this, we change the labels and rearrange/show/hide the various textboxes so it looks "normal" to them. But it all goes into the db the same way, i.e., town=city, post code=zip, county=state, etc.

City/State/Zi
City/Province/Post Cod
City/Post Cod
Town/County/Post Cod
Post Code/Cit

 

[Peggy L]

----- Jay Vinton wrote: ----

We have customers all over the world but the table design is standard

Address
Address
Address
Cit
Stat
Zi
Countr

The difference is in the UI. They can choose to disply different formats according to their local custom. To do this, we change the labels and rearrange/show/hide the various textboxes so it looks "normal" to them. But it all goes into the db the same way, i.e., town=city, post code=zip, county=state, etc.

City/State/Zi
City/Province/Post Cod
City/Post Cod
Town/County/Post Cod
Post Code/Cit

Hi Jay

This is a great help... but I'm still a little confused. If I run a report, how do I accommodate the fact that we show our zip code after the state, but in Europe they show the post code first in many instances?

 

[John Nurick]

Hi T'Kai,

You're in luck - or maybe not! The Universal Postal Union has recently
approved international address standard UPU S42. If you want to build a
system that will be compatible with all international addresses and
postal delivery point databases in every country that has one, start
reading here: http://www.idealliance.org/news/2004/ci0422.asp.

For useful practical information including examples of addresses from
many countries, see http://www.columbia.edu/kermit/postal.html.

There's no one "correct" set of fields. It depends what the database
needs to be able to do.

If it is absolutely certain that you will only ever want to sort by
country, use two fields: one for everything except the country, and one
for the country (with a lookup table with a list of countries to ensure
that they are entered consistently). This is neat and simple: you just
need to set up your labels etc. so that the country is only printed if
it is different from the country where you are.

(But get management to sign in blood that they understand that it won't
be possible to sort any other way: otherwise, sure as eggs is eggs in a
year or two someone will tell you to make it sort by city or postcode
too.)

If you do need to sort by city or postcode, things get much more
complicated because there are all sorts of variations on the US-standard
City State Zip
address line. For instance, most European Union countries use
CountryCode-PostCode City
so if you're keeping these elements in separate fields you have to write
cunning code to assemble them in the correct order depending on the
country.

I have been asked to create a database capable of handling both

international and US addresses. Does anyone have any idea how to handle
this efficiently? Keep in mind that letters, label, etc. will need to
be produced. I was considering creating a 255 character text box and
just let the user dump the entire address here. However, this does not
help with filtering if a mailing needs to be send to only a specific
state or country. Any suggestions would be appreciated.

 

[Jay Vinton]

Hi Peggy

I may be out on a limb because this is an Access forum. I use Access only as the back end to VB and I know nothing about Access reporting, but here are my thoughts

1. If Access suports it, you could use copies of the same report with different layouts for the address based on the locale, which you get from the OS or a user preference setting. At runtime, you would load the appropriate report

2. If you use VB/Crystal, you can do the same thing with different external CRW reports. Unfortunately, both #1 & #2 include a lot of maintenance overhead

3. If you use VB/Crystal with the embeded report designer (RDC), you can customize the address layout in the report code at runtime based on the locale. This is what I do because it means only one report, which can adapt itself at runtime

I don't know whether #1 will even work if you're working with Access/VBA. #2 is the quick and dirty approach. #3 will take some time & effort to build, but is extensible and easy to maintain