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@yahoogroups.com] On Behalf Of GlennL
Sent: Thursday, April 12, 2012 6:07 PM
To: MS_Access_Professionals@yahoogroups.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, 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.
Tidak ada komentar:
Posting Komentar