Zipcode Lookup Autofill

[jgalloway]

I thought about it and would it be better to keep my Zip, State, and City in one table instead of 3 like I had before?

[June7]

It would probably work just fine as one table. Not strictly normalized data because of the repetitive values in fields, which you have anyway in the ZipCodes table (City and StateCode field). You just have to decide how far you want to carry out normalization.

The RecordSource for the SalesReps form would still involve two joins to same table.

[jgalloway]

Here is the updated zip, state, city, county.

[June7]

Okay, you have not implemented my suggestion. Do you want to repeat the city/state/county information in the SalesReps and AccountManagers tables or do you want to use relational link?

[jgalloway]

I changed the table to show all of the citystatezip in just one table. I figure this would be simpler and keeping information in just one area. What ever would be the best way.

[June7]

The 'best' way is to take advantage of the relational characteristics of Access database as much as needed to make life less frustrating. This will eliminate the duplication of city/county/state/zip data in SalesReps and AccountManagers tables. You will not have to manually enter the data repeatedly nor have to implement any code to automate entry. Yes, the single zip table has a lot of repeated data but only have to do it once. Once the lookup table is built it is done, unless USPS adds new zip codes, or the county boundaries are redrawn, or add a new city record. How often will these events happen?

You can't use the zip code as primary key. You need to add an autonumber field to the ZipCodeCityStateCounty table and this will become the primary/foreign key link with the other tables. So need number field in SalesReps and AccountManagers for the foreign key, maybe call ZipStCityCty or ZSCC, whatever.

Just gotta decide.

BTW, be aware Alaska doesn't have counties, they are Boroughs (Louisiana has parishes).

[jgalloway]

Sorry but I am VERY new. Seriously I don't understand what you are really wanting me to do here. What would be the way that you would do this and how to build the relationship or what exactly would you do to make this work?

I am at my wits end. I hate asking this but can you do this and this way I can look at how you got this to work. Then I can start to understand.

Again I am very sorry for asking this but I am getting more frustrated without understanding.

[June7]

What you need is an understanding of relational database concepts. What primary and foreign keys are and how to use them. Then the instructions I have already offered should be elementary. This is basic Access functionality.

For starters, review the 'sticky' thread tutorials here http://forums.aspfree.com/microsoft-access-help-18/

If I were doing this project:
1. Need a unique value in the ZipCodeCitySateCounty table to serve as primary key. Autonumber field will serve.

2. Need fields in SalesReps and AccountManagers to hold the Zip table primary key value as a foreign key. This will establish a link between the tables.

3. Fix the SalesReps RecordSource to be a query that joins tables - as previously described.

4. Bind textboxes on the form as previously described.

5. Have multi-column comboboxes on the form to select the correct Zip record. These comboboxes will be bound to the foreign key fields. This will save the linking value so records can be related.

Alternative is to save zip/city/state/county into every record as you currently have set up. Means either manually selecting each value or writing code to find the values. Both are aggravations I would avoid. I would use the relational arrangement.

[jgalloway]

Thanks but I am not getting this. Thanks to everybody for the help and especially to you June7. I give up even though I understand this is beginners stuff but I've went through all of the tutorials and can't find anything that will help.

[June7]

You were starting to get the idea of relational concept because you did set up the Reps and Managers correctly. The location info was a bit more complicated but still uses same principle. So maybe seeing will help. Attached is a revision of your db demonstrating what I suggested. I will remove it in a few days or after you respond.

The zip code table had duplicate records, almost double, I eliminated the duplication.

EDIT: Purpose served, file removed.

[jgalloway]

Okay it did plop the switch. I understand what you were talking about after seeing this. I was trying something different instead of making the drop down box in one area. I was trying to just go off the zip code at first.

THANK YOU!!!