If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
International Addresses
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.
|
#2
|
|||
|
|||
International Addresses
"=?Utf-8?B?VCdLYWk=?=" wrote in
: 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 |
#3
|
|||
|
|||
International Addresses
We have customers all over the world but the table design is standard:
Address1 Address2 Address3 City State Zip Country 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/Zip City/Province/Post Code City/Post Code Town/County/Post Code Post Code/City |
#4
|
|||
|
|||
International Addresses
----- Jay Vinton wrote: ----- We have customers all over the world but the table design is standard: Address1 Address2 Address3 City State Zip Country 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/Zip City/Province/Post Code City/Post Code Town/County/Post Code Post Code/City 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? |
#5
|
|||
|
|||
International Addresses
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 he 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. On Tue, 1 Jun 2004 09:11:07 -0700, "T'Kai" wrote: 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. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#6
|
|||
|
|||
International Addresses
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. |
Thread Tools | |
Display Modes | |
|
|