Auto Enter County, State & Zip after entering City

REACH.Hawley

In support of everyone here trying to tell you that you need to understand more about normalization and database design fundamentals, you need to listen closely to each of these folks. 

Here are some tips about defining your data tables:

Each table should be devoted to a specific subject, such as employee addresses, customer orders, shipping methods, or suppliers. Each person or thing that belongs in a table's subject, and the data about that person or thing, forms a record.

Each specific type of information about a person or thing, such as last name, address, or phone, is a field. For example, Phone is a field in the Shippers table.

Each field and record should be unique.  The entire purpose of a relationa database is to eliminate redundancy and the possibility for data entry errors.  Suppose you did as you plan and you enter the same information repetively for address info (e.g. State, City, Zip.)  If you make a mistake just one time while typing the same City for a new record, then when you attempt to query for records using that City, the one with the mistake will be left out of your results.  Now suppose you've got multiple data entry personnel who each makes a mistake somewhere along the line in typing.  For every mistake, those records will be ignored in a query.

Entries in a single table should all be of the same kind. For instance a table of Shippers should contain only names of shippers and data about shippers.

Before you create your database, you should analyze your data and determine how it can be divided into well-structured separate tables.

 Here are just some of the benefits of a relational database and separating your data into tables:

·        Efficiency    You don't have to store redundant information, such as a customer's name or address, in every order that the customer places.

·        Control    It's easier to update, delete, and extend data in a well-structured database that doesn't contain duplication.

·        Accuracy    By avoiding repetition, you decrease the opportunity for errors. Right once, right everywhere.

·        Data integrity    You can add or remove fields or records in unique tables without affecting your data structure, and you will not need to redesign your database.

Separation protects your original structure. As you plan your tables, think of ways to structure your data so that it's easy to enter and maintain.

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

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.