Auto Enter County, State & Zip after entering City

you may want to read this

Principles of Normalization

Fundamentals

before continuing on.

Bob Larson, Former Access MVP (2008-2010)

Access is a RELATIONAL database application and is not like Excel which is a spreadsheet.

You start with the highest order of an item in a table -- truck -- identifying it by make, model, color, owner, etc.  Then layered below that is a related table of the trucks components -- engine, transmission, wheels, lights, etc.  In the next lower table are records related to the components -- (engine) - alternator, waterpump, starter, cooling fan, etc.  Next downward, again related -- (alternator) - rotor, stator, brushes, diode assembly, housing, stator windings, etc.

In identifying the alternator brushes you do not have to record that it is for a Seminole 4-cyclinder Supersport but identify the the model of alternator which has a relation to the engine and in turn the truck.

This is done using primary key fields that contain unique identification and then subordinate records use the same identifier as a foreign key.

You set a one-to-many relationship between the tables.  In your form/subform you set the Master/Child links using those primary/foreign key fields.  As a new record is created in the subform the matching foreign key is added to the subordinate table.

Build a little, test a little.

So my problem is, that since this database is still in infant stage, and I'm not far behind it Access-knowledge wise, I ONLY have tables for entering data (and have A LOT of data to enter, so waiting for the level 2 class is NOT an option anymore...) so if I can't see all information about one individual on one table I don't know where else to put it so it stays with that individual. I'm very familiar with excel, so I'm creating these tables the way I would in excel. I only got the intro to Access course, and that was a couple of months ago, so I didn't get much more than a description of the 4 major parts of Access.

As far as the city vs. zip code debate, there will only be ONE instance where two cities have the same zip code, and that will not be an issue since I want the zip code populated after entering the city. Our clients are in rural MN and there is only one zip code for each town (which are so small that many combine together to get enough kids for one school!)

I would create a table identifying the data I want entered for each city in our service area.

It is sounding as if my desire to make data entry faster will not be realized.

I thank you all for your help.

What if I WANT the County, State and Zip stored in with each record?

You should NOT do so.  That VIOLATES the rules of normalization and so you just DISPLAY them as you can get them with a query anytime.  Although I would store the CITY and ZIP CODE because a city, like Hans wrote, can have multiple zip codes and also some zip codes can actually split across towns depending on where they are.  I know because Tukwila, Washington and SeaTac, Washington actually share 98188. 

So, just so you know - you can use a QUERY in 99% of the places where you would use a TABLE so you don't really need to store the County and State IF you have the associated values stored in a reference table.

Bob Larson, Former Access MVP (2008-2010)

There can be multiple zip codes for a city, so you can't automatically fill in the zip code once a city has been entered or selected.