473,708 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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??

Nov 13 '05 #1
9 2015

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

Nov 13 '05 #2
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********@com pumarc.com> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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

Nov 13 '05 #3
"Mike McGee" <te*****@sbcglo bal.net> wrote in
news:Ns******** *******@newssvr 12.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
Nov 13 '05 #4
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

Nov 13 '05 #5
On 12 Jul 2005 10:35:34 -0700, ji********@comp umarc.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


Nov 13 '05 #6
Tom van Stiphout wrote:
On 12 Jul 2005 10:35:34 -0700, ji********@comp umarc.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 counterintuitiv e 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

Nov 13 '05 #7
On Tue, 12 Jul 2005 22:19:58 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:
"Mike McGee" <te*****@sbcglo bal.net> wrote in
news:Ns******* ********@newssv r12.news.prodig y.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

Nov 13 '05 #8

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

Nov 13 '05 #9
Chuck <li*****@school link.net> wrote in
news:j8******** *************** *********@4ax.c om:
On Tue, 12 Jul 2005 22:19:58 GMT, "David W. Fenton"
<dX********@bw ay.net.invalid> wrote:
"Mike McGee" <te*****@sbcglo bal.net> wrote in
news:Ns****** *********@newss vr12.news.prodi gy.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?


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
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2640
by: Fabio Papa | last post by:
Hi, I am fairly new to programming and and even newer to dotnet. I appoligise in advance if this is a dumb questions, and I would appreciate if you could answer it anyways. :) I am writing a windows service wich sends emails to customers at a specific time. My app retrieves cities and the city's associated time from a database, waits until the specified time for each city and sends out the appropriate emails. Below is some code that is...
0
2244
by: Malcolm Cook | last post by:
I've discovered: Using "File > Print Preview" in a form's datasheet view with a server Filter crashes access after previewing... ....that is, unless ServerFilterByForm has been turned off after applying the filter. See the steps to recreate bug below for details.
1
1309
by: Kristof | last post by:
Hello, I have a question about relations. I have a table with city's and zipcodes. I need to insert these in an other table when filling in a form. So far all works fine. But now i want it to automatically fill in the city when i fill in the zipcode or vice versa. How do I do this? Greetings, Kristof
5
7864
by: j.mandala | last post by:
Someone is trying to run my Access 2002 database under Access 2003. He has had a number of problems: 1) i used the MSComCt2.ocx for it's Date and Time picker. I can't find it under 2003. Do I need to send it to them? 2) I have a function to fill a table with values, that store the page and column numbers of a display of staff members. I had him check the link to Microsoft DAO 3.6 Object Library under references, but it still won't...
5
6109
by: ND | last post by:
I need to create a separate field from 4 fields, "street address", "city", "State" and "zip code". For example, Street address - 100 Forest Street City - Seattle State - WA Zip - 05555 Would become:
1
2923
by: Abareblue | last post by:
I have no clue on how to insert a record into access. here is the whole thing using System; using System.Drawing; using System.Collections; using System.ComponentModel;
6
1619
by: JeffB | last post by:
I have tried several different methods of getting a datagrid to fill with information. Below is the code I'm now using. When viewed in the browser and the text box filled with a parameter value (i.e. CA for Calif), the datagrids header is displayed when the button is clicked, but no data is displayed. I used SQL Profiler and can see that the query comes across with the parameter, and if I take that query string and use it exactly in Query...
2
1787
by: SKarnis | last post by:
We are trying to rebuild a current Access 2002 (XP) mdb program to VB.NET with a SQL database - we are having problems with a suitable combobox. There are many threads discussing multiple column comboboxes in .NET. We are having success with the multiple columns similar to the combobox from Access 2002 (XP). Our biggest problem is speed. In a form in Access 2002, our combobox is able to load data in a table in separate Access database...
6
1731
by: ET | last post by:
Dear All, I have once made a mdb file using access 2000 under Windows 2000. I didn't use it for long. Today I tried but some codes just didn't work. I am using Access 2002 and Windows XP. I have absolutely no idea what's going on. These are the problematic codes, thank you very much for your kind suggestion and help. Paul From Bratislava, Slovakia -------------------------------------------
0
8697
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9289
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9158
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9001
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7921
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4454
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4712
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3151
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2096
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.