Thanks John,
In the back of my mind I am thinking internationally. I know there are many
place names that occur in several countries, Vancouver, B.C. and Vancouver,
Washington, Paris Ontario and Paris France, Sudbury Ontario and Sudbury
Massachusetts, to name a few. I am re-thinking my approach as yours
definitely seems to have merit but I am still trying to convince myself that
I should make the change.
Glenn
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: April-12-12 12:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: Importing Data
Glenn-
What good is a city lookup if it's not associated with a State/Province
and/or
ZIP/Postal Code? There are tons of duplicate city names in the US - El Paso,
Springfield, Paris. Not so much in Canada, but I bet there are multiple
towns
with the same name. ZIP or Postal Code uniquely identifies a city /
State-Province location. As you pointed out, it's even more accurate in
Canada.
When I've done this sort of lookup, I make the user enter the ZIP, and then
my
lookup table automatically provides the default city / state, which the user
is
free to correct. I include a sample in many of the databases for my 2000,
2003,
and 2007 books.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
---------------------------------
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of GlennL
Sent: Thursday, April 12, 2012 6:07 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] Re: Importing Data
John,
I was interested in your comment about not having a lookup on city and state
but
rather have a ZIP lookup to help fetch the default city and state.
My question is whether you would follow the same strategy for postal codes
structures that use a different strategy than the U.S. system? For example,
in
Canada, the postal code system does a much finer geographic breakdown than I
understand is possible with the U.S. system.
A code consists of 6 characters in the format ANA NAN (giving Santa Claus
the
unique code H0H 0H0 :) ). The Post Office refers to the first three
characters
as the Forward Sortation Area (FSA). Small communities have only one FSA but
larger cities may have several. I believe each FSA corresponds to one postal
sorting/distribution centre.
It seems to me that it would be far less cumbersome to have a City lookup
table
with related State/Region and Country tables all connected by appropriate
junction tables.
I may well be over-designing, as you say, but I would appreciate
hearing your insight.
Glenn
--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
<JohnV@...> wrote:
>
> Bill-
>
> It's not normal to build a lookup on city and state. I have built a lookup
on
> ZIP code that then fetches the default city and state for the selected
ZIP.
You
> may have overdesigned your database.
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar