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
....
6  9905 
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
...
"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).
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
...
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.
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
...
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 fillcitycombo() function to fill
|
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.
Shelly
|
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 the points). So, if we take the earth
as a sphere and have longitude and latitude of any two cities,...
|
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
|
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.
| | |
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 Miami
PA Tampa
FL St...
|
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
|
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
that when I click on a state and then say I picked the wrong state and
clicked on another state in my...
|
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
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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,...
| | |
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...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
