Auto Enter County, State & Zip after entering City

Continuing with Appollo's post, also have a tblType -- PrimaryKey (Autonumber) AsocType (text) (Volunteer, Counseling Client, Food Pantry Client), etc within the Contact table.

Add a field in Contact table,remembering everyone is a Contact, for Sponsor (HeadOfHouseHold) which would be a foreign key to some ContactID.

Use Form/Subform with Master/Child link set using primary/foreign key fields.  You scroll to a Sponsor in form and add subordinates in the subform.  The primary key of Sponsor is automatically inserted in the subform (as foreign key) as you add records.

Build a little, test a little.

It's not a problem at all.  We are all just trying to help you understand that redudancy of data is not a good thing in database design.  It's unecessary work, it allows for error issues and lost data results.

Take a look here for some good training provided by the folks at Microsoft:

http://office.microsoft.com/en-us/access-help/CH010372755.aspx

Understand that when you get a better idea of normalization, your life will become much easier in this arena.  Take for instance your current issue.  Rather than trying to type the same information time and time again for City, State, Zip; consider that it would be easier if all you had to do was select a zip code and the rest of the information is readily available to display with your record.  That doesn't mean that the data would be replicated with each corresponding record, but you can easily reference and display if it were as such.

As someone mentioned above; a particular city can have multile zip codes.  Now to confuse you a little bit here, every state has several cities that may have the same name as in other states.  As long as you are talking about the US only, you have 51 states that are unique.  So you start with your States and build a table.  Then you would have a table of Cities and a reference to the state each belongs to.  Then you would have a table of Zip Codes and the Cities each belongs to.  Does this make sense so far?

Here's an example:

Table: States

 Table: Cities

 Table: ZipCodes

Table: ContactInfo

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

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