Zip Codes

[Taylor Francis]

How can I use a table of zip codes which needs to link to multiple
tables? For example, A list of businesses links to it and so does a
list of individual contacts. How does that work in the relationship
setup. I tried just doing direct links, but Access wanted to use the
zipcode table to link individuals to businesses.

I'm relatively new to Access...what am I missing?

Taylor

 

[Al Camp]

ZipCodes should not be used to link Business and Contact tables. They should each have
their own unique key field, like
tblBusiness tblContacts
BusinessID ContactID BusinessID

The Business table should be related to the Contact table via the BusinessID.

tbl ZipCodes is usually just a value table that provides the user with zipcode values to
enter in a form.
(User selects a zip from a combo based on tblZipCodes, and the City and State can be
derived from that.)
It's usually not a "relating" field.

 

[Joseph Meehan]

How can I use a table of zip codes which needs to link to multiple
tables? For example, A list of businesses links to it and so does a
list of individual contacts. How does that work in the relationship
setup. I tried just doing direct links, but Access wanted to use the
zipcode table to link individuals to businesses.

I'm relatively new to Access...what am I missing?

Taylor

Please try to post a question only once. If you need to add information
you can do so by replying to your original message.

Note since I already replied to your second message (I view them newest
first) I suggest you work with it and let this thread die.

 

[Taylor Francis]

OK, I got the combo box working, how to I extract city and state from
tblZipCodes in a report... it says my tables (businesses and
tblZipCodes) aren't related...

Taylor

 

[Al Camp]

Well, each business record should have it's own City, State, and ZipCode already,
determined when the record was created.
But, if not, link the ZipCode table to your Business table in the query behind the
report... via the zipcode. (I'm assuming your Zipcode table has appropriate City and
State entered there)
Then the associated City and State info can be placed on the report.

 

[Taylor Francis]

OK... that's working...actually too well. In the case where a zip code
has multiple valid city names each business record is repeated for each
city name. How can I make it match a specific zipcode/city combination?

I realize this is probably a basic question, so if you wanna just point
me in the right direction, I can look up stuff in books, online, etc, I
just need to know the technical terms that'll get me where I need to be...
Thanks,
Taylor

 

[UpRider]

Download http://www.dbtc.org/ftp/ziplookup.zip for a sample DB that has a
zipcode table bound to a form that will fill in the city and state when a
zipcode is typed. If a zipcode covers more then one city, a popup will allow
you to choose the proper city. The zipcode table contains about 400 zips,
mostly in Colorado. It can be expanded to any size you wish.
hth, UpRider