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.

 

Good Info!

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

I should also mention that I'm SO very new to Access and this is a new database I'm creating to replace an old and very icky one that I do not have any forms or reports created yet. I'm still hoping I'll get sent to Access 2010 level 2 before I create those...

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

If you have that information entered in a table, then you don't need to have Access AutoEnter that information.  It CAN be DISPLAYED using a query or you can have a combo box to select the city and have some UNBOUND text boxes display the selected values.  But you don't store them in with the rest of the record.

See my sample here for how to include extra fields to display in a form.

http://downloads.btabdevelopment.com/Samples/combos/Sample-FillInFields.zip

Bob Larson, Former Access MVP (2008-2010)