473,563 Members | 2,617 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

1 ZIP code 2 Cities

Blackridge VA and La Crosse VA share a common ZIP code, 23950.
I would like to make a small table of ZIP codes, Cities, and States to only
include the ZIP codes for members of a small club. The main membership table
would only have the ZIP code and that would be related to the ZIP code in the
small table. There are members in both VA and some in NC.

Access doesn't like, with extreme prejudice, a relationship with the same ZIP
code for two Cities. There may be a way around this by making the ZIP code for
these two cities have a real, or arbitary, four digit extension. If I do this,
am I stuck with having to make all ZIP codes have a four digit extension? Is
there a better way to attack the pronblem?

All suggestions will be greatly appreciated.

Chuck
....
Nov 13 '05 #1
6 9899
How would Access know which of the identical ZIPs to use since that is what
you want to use in the join? You would not necessarily be stuck with having
all ZIPs as 5+4... even though these are numeric, it is best to handle
numeric _codes_ as text, and text is variable length in Access. Just allow
enough digits, but for all the others, only supply 5 characters.

Larry Linson
Microsoft Access MVP
"Chuck" <li*****@school link.net> wrote in message
news:d0******** *************** *********@4ax.c om...
Blackridge VA and La Crosse VA share a common ZIP code, 23950.
I would like to make a small table of ZIP codes, Cities, and States to only include the ZIP codes for members of a small club. The main membership table would only have the ZIP code and that would be related to the ZIP code in the small table. There are members in both VA and some in NC.

Access doesn't like, with extreme prejudice, a relationship with the same ZIP code for two Cities. There may be a way around this by making the ZIP code for these two cities have a real, or arbitary, four digit extension. If I do this, am I stuck with having to make all ZIP codes have a four digit extension? Is there a better way to attack the pronblem?

All suggestions will be greatly appreciated.

Chuck
...

Nov 13 '05 #2
"Chuck" <li*****@school link.net> wrote ...
Access doesn't like, with extreme prejudice, a relationship with the
same ZIP code for two Cities. There may be a way around this by
making the ZIP code for these two cities have a real, or arbitary,
four digit extension. If I do this, am I stuck with having to make
all ZIP codes have a four digit extension? Is there a better way to
attack the pronblem?


The problem is fundamentally that a ZIP code is not a key into a table
of locations: ZIP codes are neither unique nor universal, and so make
a terrible key, just as social security numbers are neither unique nor
universal, and so make a terrible key.

You can use an autoincrement field instead of ZIP code as a key into
your table of cities/states/ZIP codes, and in the UI use a drop-down
to pick one. Or you can make the entire address just a text field, and
not try to be clever by trying to factor out pieces of addresses. Or
you can misaddress mail to all but one of the cities with the same ZIP
code, and let the post office sort it out (as they doubtless have to
do anyway with lots of other mail).
Nov 13 '05 #3
You should have these tables:

TblPeople
PeopleID
FName
LName
etc
etc
Zipcode

TblMembership
MembershipID
PeopleID
MembershipYear
etc

You can have duplicate zipcodes here. You don't need a zipcode table! You can
get a member's zipcode from TblPeople. You can tell who the current members are
by querying TblMembership for th current membership year.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com

"Chuck" <li*****@school link.net> wrote in message
news:d0******** *************** *********@4ax.c om...
Blackridge VA and La Crosse VA share a common ZIP code, 23950.
I would like to make a small table of ZIP codes, Cities, and States to only
include the ZIP codes for members of a small club. The main membership table
would only have the ZIP code and that would be related to the ZIP code in the
small table. There are members in both VA and some in NC.

Access doesn't like, with extreme prejudice, a relationship with the same ZIP
code for two Cities. There may be a way around this by making the ZIP code for these two cities have a real, or arbitary, four digit extension. If I do this, am I stuck with having to make all ZIP codes have a four digit extension? Is
there a better way to attack the pronblem?

All suggestions will be greatly appreciated.

Chuck
...

Nov 13 '05 #4
On Thu, 8 Jul 2004 18:31:46 -0400, "Brian Hetrick"
<bh******@notin nedmeats.iname. com> wrote:

I understand that ZIP codes do not make good keys. The database I'm working on
is for a relatively small club where all the members live near the VA - NC
boarder. All the club wants is to be able to print a club roster. The Zip
code as a key is adequate for this job. They had been using MS Publisher. I
have never used Publisher, but was told that as the membership grew, it became
slower and slower to enter new members in alphabetical order. A database
eliminates that problem.
The problem is fundamentally that a ZIP code is not a key into a table
of locations: ZIP codes are neither unique nor universal, and so make
a terrible key, just as social security numbers are neither unique nor
universal, and so make a terrible key.

Nov 13 '05 #5
On Thu, 08 Jul 2004 21:40:18 GMT, "Larry Linson" <bo*****@localh ost.not>
wrote:

I've decided to go with the 5+4 format. Right now there are only two records
that need it, but if more should occur, the program will handle it.
Thanks for your help.

Chuck
....
How would Access know which of the identical ZIPs to use since that is what
you want to use in the join? You would not necessarily be stuck with having
all ZIPs as 5+4... even though these are numeric, it is best to handle
numeric _codes_ as text, and text is variable length in Access. Just allow
enough digits, but for all the others, only supply 5 characters.

Larry Linson
Microsoft Access MVP
"Chuck" <li*****@school link.net> wrote in message
news:d0******* *************** **********@4ax. com...
Blackridge VA and La Crosse VA share a common ZIP code, 23950.
I would like to make a small table of ZIP codes, Cities, and States to

only
include the ZIP codes for members of a small club. The main membership

table
would only have the ZIP code and that would be related to the ZIP code in

the
small table. There are members in both VA and some in NC.

Access doesn't like, with extreme prejudice, a relationship with the same

ZIP
code for two Cities. There may be a way around this by making the ZIP

code for
these two cities have a real, or arbitary, four digit extension. If I do

this,
am I stuck with having to make all ZIP codes have a four digit extension?

Is
there a better way to attack the pronblem?

All suggestions will be greatly appreciated.

Chuck
...


Nov 13 '05 #6
On Thu, 08 Jul 2004 22:35:10 GMT, "PC Datasheet" <no****@nospam. spam> wrote:

I'm not a professional programer. However, that doesn't mean I shouldn't do
things correctly. I use Northwind.mdb a lot and it is a good demonstration of
your suggestions. All I am trying to do is save typing the same city and state
many times. Zipcode is entered in the TblPeople and city and state are pulled
from the TblCityStateZip . I've already told the club that if my program does
not satisfy them, then they should pay a professional and get it done right.

I do appreciate your suggestions. Thank you.

Chuck
---
You should have these tables:

TblPeople
PeopleID
FName
LName
etc
etc
Zipcode

TblMembershi p
MembershipID
PeopleID
MembershipYe ar
etc

You can have duplicate zipcodes here. You don't need a zipcode table! You can
get a member's zipcode from TblPeople. You can tell who the current members are
by querying TblMembership for th current membership year.


Nov 13 '05 #7

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

Similar topics

12
3160
by: Bob Bedford | last post by:
I've a page where users may enter a zip code (text edit) and I want to fill a combobox with existing cities depending on the zip code entered. Let give an example. I've my text edit like: <input name="ZIP" type="text" id="ZIP" onKeyUp="fillcitycombo();"> <select name="City" id="City"></select> When entering the zip code, I'd like the...
11
2108
by: News | last post by:
I have a database of zipcodes with latitude and longitude. I also have the method of calculating the distance between two zipcodes. What I want to know is if there is an efficient algorithm for obtaining the zip codes within a specified distance of the first zipcode without having to retrieve and calculate for every record in the database. ...
1
1711
by: R. Rajesh Jeba Anbiah | last post by:
Q: How to find the distance between two zip codes/cities? Q: How to find list of cities/zip codes that are with in the particular radius from given city/zip code? A: In spherical trigonometry, we have haversine formula, which provides relationship between the radius of the sphere and any two points on the sphere (longitude and latitude of...
3
1567
by: bhavik | last post by:
Is there a database in .Net which lists all countries, cities, addresses, etc. (first browse and select from avavilable countries, then once specified, all available cities in this country can be browsed (dropdown) and finally the roads. if it is have. let me know that methods name. Regards, bhavik
4
1834
by: pbb | last post by:
Is it possible to retrieve VB code stored in SQL Server as text and execute it in code behind in an ASP.Net app? I guess what I'm looking for is an object that would have an Execute method that could be called and pass in text for the VB code to be executed.
2
1135
by: Jake | last post by:
To avoid a postback, we want to use a webservice and XMLHTTP to fetch the list of values for dropdowns whose contents are determined by an earlier user-choice on the page. E.g. if the user selects "FL" from the State dropdown, the Cities dropdown will be populated with cities in Florida only: State Dropdown Cities Dropdown NY ...
9
3435
by: Sandy | last post by:
Hello - I need either a cheap tool or code & DB that calculates, eg. within 50-mile radius of a zip code. Anyone have any suggestions? -- Sandy
1
12500
by: jason.tadeo | last post by:
I am very new to java script and I am trying to be able to have two select boxes. One that has states and one that has cities. On the change or pick of the state then the cities in my data base are they loaded. I do this using sajax (basically using java script to go back to the server with out a page refresh). Well my problem comes after...
2
1993
by: Brendon Bezuidenhout | last post by:
All, Real daft one here - Does anyone know where I can find a list of all countries, areas and cities to populate into Combo Boxes on a web form possibly? Thanks in advance Brendon
0
7658
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8101
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...
1
7630
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7940
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...
0
6228
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...
0
5198
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3626
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...
0
3609
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2075
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

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.