Zip code populate other fields table help needed (1 Viewer)

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
**My original database was built in Access 2007. I am now trying to upgrade and make changes and it is in Access 2019**
Im pretty desperate on figuring this out and have been searching the internet and asking questions on whatever forums I can...


There seems to be a lot of back and forth on typing in a zip code and having it autofill the city and state (and in my case, I'd like county). I'm not sure if there has ever been one final definitive method of getting this done. Here is what I have in place now.



tblAddress

pkAddressID

fkClientID

fkAddressTypeID

StreetAddress

fkCityID

fkStateID

Zip

fkCountyID



Here is how it works now. I have a main client form with an address subform. The city, state, and county are combo boxes. The user can start typing a name and it will autofill or drop down to select a name, or type in complete new name. There is an after update event to check if the name typed in each of these already exists and if not, they are allowed to add it. This is how the city, state and county tables were built. These tables consist of only an autonumber pk and the name of the city, state or county for their respective tables. The zip code property on my form is a text box. It is allowed to be typed in and repeated as needed. I probably should have thought that thru further.



Now I want to make it so that when a user types in the zip code on my address form, it autofills city state and county. I have a list for the whole country of zips, city, state, county, more on that later. I also have about 7,000 records already in the address table.



In researching this, I find two methods to achieve this. Put zip code, city, state, county into one table as text fields and use DLookup or Vlookup to autofill in the other text boxes on my form...not sure how this records them in the linked tblAddress because I stopped researching when I hit upon another method which is to keep the separate tables and either create a linking table or query that fills in the fields. This started going beyond my coding skills and understanding. So I thought I'd start with the basic question... Since I have this set up with those tables and normalization rules (I see now that not the zip code though, I'm unsure why I didn't do that in its own table either other than I'm still a self teaching beginner).



Anyway, my question is what is the best method to follow with in the way I have my database set up now? I have discussed a couple of things as far as putting all these tables (zip, city, state, county) into its own separate linked back end to keep the speed and size of the database optimized. I also saw in another post, that regardless of which method I use, I shouldn't just import the entire list of zip codes etc for the entire country. We wont use them. We will use portions of about six states and then a few miscellaneous ones here and there. That my look up or query should first search the zip codes I already have and then continue what we've been doing...adding them on the fly as needed. I see now that this would have optimally worked if those zip codes were in there own table. If I need them in their own table, I'm going to need to know how to change them from text field to a foreign key for that table.



I also realize that some zip codes span more than one city, state or county. Ultimately I'd like to handle that with letting the user just correct it in the combo or text box and having a message asking them if they are sure...
I think that is the simpliest way to handle that, but suggestions are appreciated.

I made this database many years agolearning as I went along with lots and lots of help from forums like this. I’ve used vba and bit here and there since then but basically starting from scratch. I really need to know first steps from what I have. I imagine there must be lots of people this could help too.


I’m leaning toward keeping separate tables and creating a linking table... is this the best method or just the most difficult?


I'd really appreciate any and all assistance that I can get with this.
Thank you,
Kathy
 

plog

Banishment Pending
Local time
Today, 06:53
Joined
May 11, 2011
Messages
11,611
Personally, I think you've over-normalized with a state table, a city table and a county table. Add to that your desire to give the user the ability to override what your tables say about which city/county go to a zip code, and I think state, city, county and zip should all be Short Text fields in tblAddress. A drop down could still be used for the state input and you would have text inputs for the others.

Now I want to make it so that when a user types in the zip code on my address form, it autofills city state and county.

This is still possible with the input types I just described. However you need a datasource that has the fields Zip, State, City and County with Zip being unique to each record. I think that should just be one table that you populate, instead of a table for each that you combine in a query (although it is possible to make a query in which you JOIN all your current tables--assuming they have fields you can JOIN) . Then you would write code that does Dlookups whenever the Zip code on your form is entered and populates your city, state and county inputs on the form.
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
That has been suggested, but since I already have so much data already set up in the tables, I was hoping to keep them.
And I’m not necessarily wanting to “override” a lot of things. It’s sometimes there is two options... such as zip code 12345 is for Johnson City which is in County Smith but also County Jones. They’d need to have the option to pick the correct one....we will know that on data entry. I was thinking I could use cascading combo boxes or something similar so that if it gets to the county with more than one choice either would be available? I’m know cities can have more than one zip code but I’m pretty sure zip codes are unique to one city. The county becomes the problem. For now though I just need to know where to START with this project. I know it seems I’ve over-normalized but once I put in all the zip codes which is I think 42,000 wouldn’t it be better to be overnormalized in that instance?
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
Also....what about my table address? It will still need to show city state county zip? Or would it just show the fk to the zip code field? Wouldn’t that be confusing? Right now my address table shoes the fk to each city, state, zip so if I need to see names in this table, I can.
 

plog

Banishment Pending
Local time
Today, 06:53
Joined
May 11, 2011
Messages
11,611
I’m know cities can have more than one zip code but I’m pretty sure zip codes are unique to one city.

I know what you meant and quick google search says zip codes can span counties and cities. And cities themselves can span counties. Rare but possible. That means you should have 1 table with zip/city/county and a primary key to hold those combinations. Let's call this tblZips.

What you described in your last post was not users overiding city/county suggestions, but validating an existing zip/city/county record goes to a record in tblAddress. The amount of records in the table has no bearing on the structure. tblZips is the way to go for zip/city/county. As for seeing all the data in one spot--build a query.


Cascading combo boxes can work, it would just require the zip code be entered first otherwise your city and county combo boxes would have a ton of choices. Another method is have the user enter the zip and click a button. Code runs to find the record:

0 matches found: a form opens that asks them to verify the zip they entered and to add city/county data, that would then go into tblZips and they could then use that zip on the main form.

1 match found: the inputs populate with the found data

>1 match found: a form opens with every match and the user can click on the appropriate one, form closes and the data is loaded into the main form

In every case tblAddress only gets the primay key from tblZips--the actual zip code, county and city come out of tblAddress.


Lastly, data migrates. I don't think you should eliminate any fields, just restructure. tblZips can work with your existing data, you just need to migrate it. Build tblZips then populate it with all the zip/city/county combinations in the existing table. Add a field in the existing table to hold the primary key of tblZips, create a query between the existing table and tblZips where you JOIN on every matching piece of data and UPDATE the existing table with the the primary key of tblZips.
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
I'd always have the zip code entered first. I do like your suggestion that we could build on our existing zip-city-state-county option without having to dump a bunch of useless zip codes in there.

As far as building tblZips, how do I populate it with every existing combination? I have Only ok's for City, state, and county in table address then the actual zip code is a text field. I was suggested to turn all those fks intro text fields? Is that necessary? Your method clears them out of table address and removes the three tables (City, State, County) is that correct? I'm not sure if I mentioned that I taught myself enough vba with lots and lots of help years ago to build this database, I really don't remember a ton of it, so this is basically like starting from scratch for me. So am I using a query to migrate data? Copying the table and creating a new primary key and deleting all the other fields? Whats the quickest way to get that done?

Also is there any code that you could point me to that would help me get started on checking for 0, 1 or >1 matches? I'm assuming I'll need to build a couple of forms first too....
 

plog

Banishment Pending
Local time
Today, 06:53
Joined
May 11, 2011
Messages
11,611
I would work in a new blank database so you don't corrupt the actual data. Copy in your tables (tblAddress, tblCity, tblState, tblCounty). Make tblZips as such:

tblZips
zip_ID, autonumber, primary key
zip_Code, text, zip code of address
zip_City, text, city name
zip_County, text, county name
zip_State, text, state code/name

Build a query with your existing tables, linked apporpriately. In the query designer bring down the appropriate field for each field in tblZips (ZipCode from tblAddress, City from tblCity, etc). Make it a Totals query (click the Sigma/Summation symbol in the ribbon) and GROUP BY every field and make it an APPEND query and put the data into tblZips. Run it and now all your data is in tblZips. Save the query as "moveQuery".

Next add a numeric zip_ID field to tblAddress so that you can link it to tblZips. Open moveQuery in design view and stop it from being a Totals query (click the Sigma/Summation symbol again). Add tblZips to the query and link it approrpiately to all the other tables (zip_Code to tblAddress.ZipCode, zip_City to tblCity.City, zip_County to tblCounty.CountyName etc.). Clear out all the fields down below and have that new field (tblAddress.zip_ID). Change the query to an UPDATE query and under zip_ID update it to the value from tblZips.zip_ID. Run that query and now your data is reconnected and you can delete city/state/county date from tblAddress and use tblZips for it.

Also is there any code that you could point me to that would help me get started on checking for 0, 1 or >1 matches?

That parts easy, it will simply be a DCount(https://support.microsoft.com/en-us...e42-be7a-11a64acbf3d3?ui=en-us&rs=en-us&ad=us). Get that intoa variable, then you would have an if/elseif/else section to test for the 3 cases and handle approriately.
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
I am going to try this exactly as you have it written a few hours. I cannot thank you enough for getting me in the right direction. I am sure I will be back with more questions later. :):)
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
"Build a query with your existing tables, linked apporpriately. In the query designer bring down the appropriate field for each field in tblZips (ZipCode from tblAddress, City from tblCity, etc). Make it a Totals query (click the Sigma/Summation symbol in the ribbon) and GROUP BY every field and make it an APPEND query and put the data into tblZips. Run it and now all your data is in tblZips. Save the query as "moveQuery"."

I tried this...it is not working. I'm guessing it has something to do with "linked Appropriately" I must not have them linked appropriately. I guess I don't understand what that means..... I hope someone sees this :(
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
I managed to get the query to build the zip code table. I had to use SELECT DISTINCT. My problem now is that I have to get that new field in table address to populate based on the new table zips. I tried following your instructions but just can't get it correct. Can you help me build the update query from scratch? Here are my tables:

tblAddress
pkAddressID
fkCLientID
fkAddressType
StreetAddress
fkCityID
fkStateID
Zip
fkCountyID
fkZipCodeID <<---this is the new field

tblZIp <--this is the new table
pkZipCodeID
txtZipCode
txtZipCity
txtZipCounty
txtZipState


Ok I need my append query to say In table zip code find a match of fkCityID,fkStateID, fkCountyID and ZipCode from table ZipCode and add the pkZipCodeID to table address in fkZipCodeID.

I know how to say that, but I do not know how to "link" them so that happens. Because now in Table Zip Code there are City Names and not the fkCityID. So how do I tell the query to match all these up? I'm really lost and trying to do this today....
 

plog

Banishment Pending
Local time
Today, 06:53
Joined
May 11, 2011
Messages
11,611
In design view bring in tblAddress and all the old tables that link to them (tblCity, tblState, etc.). Link them by foreign key to primary key as you would have in the old system. Doing that makes all the text fields that now live in tblZip available.

Next, bring in tblZip and link txtZipCode to tblAddress.Zip. Link txtZipCity to tblCity.CityName. Link txtZipCounty to tblCounty.CountyName. Link txtZipState to tblState.Statename. I probably didn't get the field names right, but you should see what I am doing, link the fields in tblZip to the fields where the data is in the old method you used.

Then in the query bring down pkAddressID and pkZipCodeID and rename them 'p_aid' and 'p_zid'. These are your matches and identify which records in tblZip goes to which records in tblAddress. Unfortunatly, you probably won't be able to turn this into an UPDATE query and just update tblAddress.ZipCodeID. What you will have to do is turn it into a MAKE TABLE query. Do that and create a 'tmp_Zips' table which will just be 2 fields (p_aid, p_zid).

Lastly, you will make a new query using tblAddress and tmp_Zips linking them p_aid to pkAddressID. Change the query to an UPDATE query. Bring tblAddress.fkZipCodeID into the query and in the Update To field put [p_zid]. Run that and you now have tblAddress related to tblZip via fkZipCodeID.
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
" in the query bring down pkAddressID and pkZipCodeID and rename them 'p_aid' and 'p_zid'."
Is renaming done in the query fields or is it done when I do Make Table?
 

plog

Banishment Pending
Local time
Today, 06:53
Joined
May 11, 2011
Messages
11,611
Its done in the query and flows through to the make table.

p_aid: pkAddressID
p_zid: pkZipCodeID
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
"pkAddressID and pkZipCodeID" I tried just using these without rename to make table and I received a message saying resultant table cannot have more than one autonumber field. How do I resolve that issue?
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
I don't mean to be dense, but queries are really hard for me. Am I typing: p_aid: pkAddressID and p_zid: pkZipCodeID into the "field" where it says pkAddressID in the query column?
 

plog

Banishment Pending
Local time
Today, 06:53
Joined
May 11, 2011
Messages
11,611
In design view, you bring them into the bottom portion, each in their own field. You can do that by typing what I put in my last post into their own blank fields at the bottom. Or you can double click on each field individually at the top and the interface populates them in the bottom portion for you where you can rename them.
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
I did that and still get the autonumber error. both pkAddressID and PkZipID are autonumbers. Do I need to temporarily change one of these from autonumber? Wouldn't the temp table make its own autonumber field?

Also, pkAddressID is not linked to pkZipID and it shouldn't be. Do I need other criteria in my query like maybe the zip code?

I'm really sorry. I just do not understand queries :(
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
So I just went ahead and removed outnumbering from pkZipCodeID. it made the temp table fine. And I think I will be able to complete the other query from your instructions. Thank you for all that.

BUT I have a different problem. When I make my New Zip Code table, I used Select distinct, so I was surprised there were duplicates of city, state, county combinations. On further study, I see they are not exact duplicates, they are typos of the same combination but they have spelled something wrong in either the city or county field.

BEFORE I attempt to recreate all this in my actual database....should I manually go in to my table address and correct the typo errors so that I will get one true unique value for each zip code combination?
 

plog

Banishment Pending
Local time
Today, 06:53
Joined
May 11, 2011
Messages
11,611
There should be no autonumber issue. You are simply making a table, not changing any autonumber.

Yes, you should fix any errors in all your address tables. Then make tblZips, then follow the process I outlined above.
 

KathCobb

Registered User.
Local time
Today, 04:53
Joined
Jun 29, 2012
Messages
46
I’ve got the fkZipCode into the table address. Yay me and double yay you for telling me how to do it.
Tomorrow I will try writing the D lookup.
I see in the Zip Code resource I’m using, they call cities that share a zip code either “standard” or “acceptable”. Would any of my coding or wanting to give the user options be easier if I included a zip code type field in my table?

Thank you again for helping me and hope you can continue to help. I’ve made a lot of notes and learned a lot doing this :)
 

Users who are viewing this thread

Top Bottom