Making access 2002 fill in the city from a zip code tbl

[Mike McGee]

I am new to database apps, but I am making a db with access 2002. Here is
what I have and what I would like for it to do.

tblCustomers = holds customer info (Name, Address, City, State, Zip, Phone)
tblzips = holds ( Zip codes, City, State, County, Country)

tblzips

ID Zipcode City State County Country

I would like to have the Enduser type in the Zip in the customers table, and
it fill in the
City State County Country automaticly for the customer table.

How can this be done or can it??

[jimfortune]

Mike McGee wrote:

I am new to database apps, but I am making a db with access 2002. Here is
what I have and what I would like for it to do.

tblCustomers = holds customer info (Name, Address, City, State, Zip, Phone)
tblzips = holds ( Zip codes, City, State, County, Country)

tblzips

ID Zipcode City State County Country

I would like to have the Enduser type in the Zip in the customers table, and
it fill in the
City State County Country automaticly for the customer table.

How can this be done or can it??

Can it be done?
Yes.

How can it be done?
Form datasheet view bound to tblCustomers with Zip AfterUpdate opening
recordset with query to tblZip

Should it be done?
That's the interesting question. You're actually denormalizing since
Zip gets you everything :-). I suppose it's good to verify that the
city is correct. Once the information is verified I don't see why you
need it in tblCustomers if tblzips is kept current.

James A. Fortune

[Mike McGee]

I think I understand thanks for the help your right why have the same
information in 2 places.
seems I need to rethink a little bit. thanks again
Mike McGee
MBR Guaranteed Foundation Repairs

<ji********@compumarc.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Mike McGee wrote:

I am new to database apps, but I am making a db with access 2002. Here is what I have and what I would like for it to do.

tblCustomers = holds customer info (Name, Address, City, State, Zip, Phone) tblzips = holds ( Zip codes, City, State, County, Country)

tblzips

ID Zipcode City State County Country

I would like to have the Enduser type in the Zip in the customers table, and it fill in the
City State County Country automaticly for the customer table.

How can this be done or can it??

Can it be done?
Yes.

How can it be done?
Form datasheet view bound to tblCustomers with Zip AfterUpdate opening
recordset with query to tblZip

Should it be done?
That's the interesting question. You're actually denormalizing since
Zip gets you everything :-). I suppose it's good to verify that the
city is correct. Once the information is verified I don't see why you
need it in tblCustomers if tblzips is kept current.

James A. Fortune

[David W. Fenton]

"Mike McGee" <te*****@sbcglobal.net> wrote in
news:Ns***************@newssvr12.news.prodigy.com:

I am new to database apps, but I am making a db with access 2002.
Here is what I have and what I would like for it to do.

tblCustomers = holds customer info (Name, Address, City, State,
Zip, Phone) tblzips = holds ( Zip codes, City, State, County,
Country)

tblzips

ID Zipcode City State County Country

I would like to have the Enduser type in the Zip in the customers
table, and it fill in the
City State County Country automaticly for the customer
table.

How can this be done or can it??

I would do it with a combo box, and have the AfterUpdate of the
combo box populate the fields from columns of the combo box.

There are completely valid reasons to duplicate the data, rather
than using a lookup to the zip code table. One such is that there's
not always a 1:1 relationship between zip code and city name. That
is, the single city name stored with the zip code will always be
correct, but it won't necessarily be the only name that zip code is
referred to by.

Most people would say I live in Astoria, NY, in Queens, NYC. But the
Post Office defaults to Long Island City as the city name in their
lookup tables. But Astoria, Long Island City and Queens are all
valid for the city in sending snail mail to my address.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[jimfortune]

Mike McGee wrote:

I think I understand thanks for the help your right why have the same
information in 2 places.
seems I need to rethink a little bit. thanks again
Mike McGee
MBR Guaranteed Foundation Repairs

You can do what I do - claim that having the info in two places will
make the queries have one less join. But that approach has burned me
in the past. I set up a database that had a tblJobs with about 100
fields. The fields belonged in that table except for three departments
with four fields of information each. A few years later, the company
liked the department information so much that they decided to get more
detailed information by having eight departments with eight fields
each. Plus backward compatibility to the old 12 fields got the table
to about 164 fields. They were pretty pressed for time so the quickest
way to implement it was to add the fields. They also liked to have
editable datasheet subforms despite my efforts to limit them. The
simpler queries allowed them to keep editing the subforms. So everyone
was happy. Recently, several of their customers have been demanding
capacity studies broken out by each department. But the new quotes
have 24 departments. I'm now forced to normalize so that all 24
departments can be tracked by hours quoted and timetickets. I had to
explain that the whole process is going to take much longer than
before. I explained that they won't be able to edit some subforms
because the extra join makes some queries too complex. Plus, the slow
transition requires that I leave all the old fields in for backward
compatibility so that all the reports will work until new ones are
made. Not to mention the backward compatibility of the backward
compatibility (they like being able to go back pretty far). So if I
seem to be harping on normalization it's because I just went through a
painful lesson. I still don't like normalization to the nth degree but
my tradeoff point is fluxing towards more normalization. On top of all
that, one secretary is on a crusade to have ever larger forms where any
and all information is obtainable. Think in the neighborhood of
hundreds of fields on the main forms. It seems that prevailing office
practices denormalize databases and work toward degrading performance.
I've tried explaining how it works to them but they prefer convenience
to speed.

James A. Fortune

[Tom van Stiphout]

On 12 Jul 2005 10:35:34 -0700, ji********@compumarc.com wrote:

Zip doesn't get you everything. At least in the USA there is a 1:M
between Zip and City. There also is a 1:M between City and State.
-Tom.

Mike McGee wrote:

I am new to database apps, but I am making a db with access 2002. Here is
what I have and what I would like for it to do.

tblCustomers = holds customer info (Name, Address, City, State, Zip, Phone)
tblzips = holds ( Zip codes, City, State, County, Country)

tblzips

ID Zipcode City State County Country

I would like to have the Enduser type in the Zip in the customers table, and
it fill in the
City State County Country automaticly for the customer table.

How can this be done or can it??

Can it be done?
Yes.

How can it be done?
Form datasheet view bound to tblCustomers with Zip AfterUpdate opening
recordset with query to tblZip

Should it be done?
That's the interesting question. You're actually denormalizing since
Zip gets you everything :-). I suppose it's good to verify that the
city is correct. Once the information is verified I don't see why you
need it in tblCustomers if tblzips is kept current.

James A. Fortune

[jimfortune]

Tom van Stiphout wrote:

On 12 Jul 2005 10:35:34 -0700, ji********@compumarc.com wrote:

Zip doesn't get you everything. At least in the USA there is a 1:M
between Zip and City. There also is a 1:M between City and State.
-Tom.

That's interesting. I didn't know there was a 1:M between Zip and
City. It seems a little counterintuitive but if it's so, you have to
deal with it if you have any like that in the database. It makes sense
that a zip code can handle multiple cities. A 1:M between City and
State is O.K. as long as there's no 1:M between Zip and State. Since
the improbable is likely :-) he's better off with human input I guess,
or at least editing out the other cities that show up for that zip
code.

James A. Fortune

[Chuck]

On Tue, 12 Jul 2005 22:19:58 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Mike McGee" <te*****@sbcglobal.net> wrote in
news:Ns***************@newssvr12.news.prodigy.com :

I am new to database apps, but I am making a db with access 2002.
Here is what I have and what I would like for it to do.

tblCustomers = holds customer info (Name, Address, City, State,
Zip, Phone) tblzips = holds ( Zip codes, City, State, County,
Country)

tblzips

ID Zipcode City State County Country

I would like to have the Enduser type in the Zip in the customers
table, and it fill in the
City State County Country automaticly for the customer
table.

How can this be done or can it??

I would do it with a combo box, and have the AfterUpdate of the
combo box populate the fields from columns of the combo box.

There are completely valid reasons to duplicate the data, rather
than using a lookup to the zip code table. One such is that there's
not always a 1:1 relationship between zip code and city name. That
is, the single city name stored with the zip code will always be
correct, but it won't necessarily be the only name that zip code is
referred to by.

Most people would say I live in Astoria, NY, in Queens, NYC. But the
Post Office defaults to Long Island City as the city name in their
lookup tables. But Astoria, Long Island City and Queens are all
valid for the city in sending snail mail to my address.

If you use the 5+4 zip code does that limit the mail to only Astoria?

There are three small towns in VA that share one post office. The first 5
digits gets to the post office. The next 4 digits determines the mail carier
route.

Chuck

[jimfortune]

David W. Fenton wrote:

...
I would do it with a combo box, and have the AfterUpdate of the
combo box populate the fields from columns of the combo box.

I agree. I have done it this way for Access and for ASP. Bringing in
several fields at once and using the combobox/custom control as a local
repository saves having to retrieve the information from the table
after the zipcode (custcode in my case) is chosen.

James A. Fortune

[David W. Fenton]

Chuck <li*****@schoollink.net> wrote in
news:j8********************************@4ax.com:

On Tue, 12 Jul 2005 22:19:58 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Mike McGee" <te*****@sbcglobal.net> wrote in
news:Ns***************@newssvr12.news.prodigy.co m:

I am new to database apps, but I am making a db with access
2002. Here is what I have and what I would like for it to do.

tblCustomers = holds customer info (Name, Address, City, State,
Zip, Phone) tblzips = holds ( Zip codes, City, State, County,
Country)

tblzips

ID Zipcode City State County Country

I would like to have the Enduser type in the Zip in the
customers table, and it fill in the
City State County Country automaticly for the
customer table.

How can this be done or can it??
I would do it with a combo box, and have the AfterUpdate of the
combo box populate the fields from columns of the combo box.

There are completely valid reasons to duplicate the data, rather
than using a lookup to the zip code table. One such is that
there's not always a 1:1 relationship between zip code and city
name. That is, the single city name stored with the zip code will
always be correct, but it won't necessarily be the only name that
zip code is referred to by.

Most people would say I live in Astoria, NY, in Queens, NYC. But
the Post Office defaults to Long Island City as the city name in
their lookup tables. But Astoria, Long Island City and Queens are
all valid for the city in sending snail mail to my address.

If you use the 5+4 zip code does that limit the mail to only
Astoria?

Well, that I don't know. It depends on your lookup table.

My post office is called "Long Island City" but much of the coverage
area of the 5-digit zip for it is Astoria. And both are sub-sections
of Queens, which is also valid.
There are three small towns in VA that share one post office. The
first 5 digits gets to the post office. The next 4 digits
determines the mail carier route.

I am certain that there are many mail carriers working out of my
local post office. Some may even have routes that are partly in
Astoria and partly in Long Island City.

Of course, there are no hard and fast borders between the two --
human beings may make different determinations of where the boundary
lies, and the zip code system allows for this by having a
non-exclusive relationship running from 5-digit zip to city.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc