Auto Enter County, State & Zip after entering City

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

There is a right way and wrong way to create a database. But there are sometimes methods in between. You have mentioned to use that you are very new to Access. And that's fine. You are doing the right thing by posting on a site such as this and asking for advice. My issues is when you get that advice you don't want to use it.

Relational databases, like Access, were first developed in the mid 1970s. The rules for design of such databases has not changed since then. In fact, the rules of normalization that define relational database design is the de facto standard. There are other types of designs, but the Relational model is , by far, the most popular and most efficient.

So when you ask us how to do something that is not normalized our answer will be to not do it. I've written a blog on how to display values from a related table on a form. That's the proper way to do it.

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

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.

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.

you may want to read this

Principles of Normalization

Fundamentals

before continuing on.

Bob Larson, Former Access MVP (2008-2010)

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

I thank you all for your help.

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.

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