Auto Enter County, State & Zip after entering City

Apollo67

I really am doing my best to listen-that's why I'm on here, but some of the terms are going right over my head right now since I only have Level 1 Access training...

I read thru the link about normalization...I think I'm following that as best I can with the data I need to track. I do have LOTS of tiny tables. I run into issues when I'm told I shouldn't keep City, State, Zip and County for individuals in the same table as their address as I look at the Sample Table in Access for Students and they have all that data in one table...

I'm trying to track data for the Non-Profit I work at. We need to track Volunteers, Counseling Clients, Food Pantry Clients and all of the times they visit here. I have a lot of non-related info and also a lot of info that actually does tie together. One major issue I see coming is the fact that we track every member of each household for the food pantry (for the food bank numbers and to make sure no one is double-dipping) and each member is going to have the same address and home phone.

What I had actually done before I started hand entering the active counseling client data (another story) was create all of the tables and relationships to see if I had everything. I then broke the extensive tables down into smaller tables. I believe I have it as small as I can right now, but I could be wrong.

I really hope I can get moving on this database this week since it's fairly slow in the office and I can really work on it. My last resort is waiting until the end of January for Access Level 2 training and hope that does the trick.

I really do appologize if I'm acting rude or childish or in some other manner that offends someone who is offering their help to me. I truly appreciate any and all help I can get.

Thanks

Microsoft does not always follow best practices design techniques in templates and examples. Most of the people who help on this and other Access support sites DO try to follow best practices in our advice and recommendations.

I'm curious as to what the level 1 and level 2 training covers. Level 1 should probably cover table, form and report design.

The point to normalize is generally when you find data being repeated multiple times. So if you have several people with the same address, then you should have a separate address table and use a Foreign Key in your Household members table that links to the address. Actually you should have a household table linked to or including an address and a members table that links to the household record and you can get the address from there.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Scottgem [MVP] wrote in

news:Email removed for privacy

m:

Actually its just the opposite. A relational database makes data

entry EASIER because you don't have to enter redundant info. Yes

you enter the zip code in your main data table. And that's all you

enter. To see the County State, Town you do a join to your zip

code table. If you look at the blog I mentioned it may help you

understand this better.

I'm going to call foul here on this one because I've never once

implemented a data structure that assumes that a zip code can be

used to populate the city and state fields. For one, you may not

have the zip code. Secondly, a single zip code can cover multiple

cities (though never multiple states).

To me, implementing it the way you suggest would be

overnormalization and I've never seen an application implemented

that way.

I'm surprised so many people are even beginning to suggest this!

---

David W. Fenton                  http://www.dfenton.com/ contact via website only     http://www.dfenton.com/DFA/

I have, on more than one occasion. I built a database for a client that also assigned sales rep to a contact based on zip code ranges. The client requirement was to enter a Zip code and have the City, region and country displayedand and to assign a sales rep. That being said I do agree it may be overnormalization. On the other hand, I think that part of the reason that its being suggested here is to get the OP on the right foot. I'd rather have a newbie understand about normalization and then decide when it is overdone then confuse them with the concept of overnormalization.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Hi Bob,

Just wanted to share with you a few things that I checked on and some additional information regarding the model I have provided as exampleonly here. 

The first address you provided as example does not come up as valid in mapping applications: Mapquest or MS Streets And Trips.  However, change the 'W' to 'S' and the address comes up. 

Now, in Mapquest the address is shown as:  15210 Tukwila International Blvd,Tukwila,WA 98188-2246

In MS Streets And Trips the address is shown as: 15210 Pacific HWY S, Seattle, WA 98188

The second address you provided as example also gives different results depending on the application:

In Mapquest:

3120 S 176th St,Seatac,WA 98188-4006

In MS Streets And Trips:

3120 S 176th St,Seattle,WA 98188

In referencing this information from the US Postal Service the following was provided:

Actual City name in98188

SEATTLE , WA

Acceptable City names in98188

SEATAC, WA

TUKWILA, WA

Not Acceptable

DUWAMISH, WA

MCMICKEN HEIGHTS, WA

RIVERTON, WA

For others reading this, in regard to the model which I provided, a simple change would be necessary to provide an arbitrary ID as the Primary Key for the Zip Code and City.  A Zip Code can then reference more than one City.  If the user were to use a combo box to lookup City and State by Zip, they would simply include the Zip with City name as the reference.  In this case I might create another table for Zip Codes alone to ensure referencial integrity. 

ZipCodes

ZIPCities

So in final thought I'd say that depending on who you ask, Zip Codes may or may not cross City boundaries.  Once again, the Postal service accepts the major City, in this case being Seattle, as the correct City related to the Zip Code.

Yes, true, but in all actuallity, using the major city will work just the same for the governor.  Furthermore, there is the ZIP+4 system which refines the delivery location.

Bob, what can I say I guess you have me over a barrel.  I've done extensive research in this area and though I'm still no expert, Zip Codes are not commonly found to cross city boundaries.  SEATAC became a city in 1989 and that might explain why there is some ambiguity there.  I really don't have an answer.  The Zip Code alone when referenced is a Seattle locale.

Yep, I know that the incorporation date has something to do with it (as the Postal Service didn't make someone change to a new zip code), but it shows that it CAN exist so you can't assume that a single zip code is for a single city.

Bob Larson, Former Access MVP (2008-2010) http://www.btabdevelopment.com (free Access tools, tutorials, and samples)