1 ZIP code 2 Cities

[Chuck]

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

[Larry Linson]

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*****@schoollink.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
...

[Brian Hetrick]

"Chuck" <li*****@schoollink.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).

[PC Datasheet]

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******@pcdatasheet.com
www.pcdatasheet.com

"Chuck" <li*****@schoollink.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
...

[Chuck]

On Thu, 8 Jul 2004 18:31:46 -0400, "Brian Hetrick"
<bh******@notinnedmeats.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.

[Chuck]

On Thu, 08 Jul 2004 21:40:18 GMT, "Larry Linson" <bo*****@localhost.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*****@schoollink.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
...

[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

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.