Update Query using Zip Codes to Set County Field

[b_rye_chan]

I'm building a Contacts database right now. I have a tblContacts with fields "Zip Code", "County", and "Region". Every record has a zip code but the County and Region are empty. I'm trying to build an update query that will take a zip code and match it to a zip code stored in tblZipCodes, and update [tblContacts]![County]. I may have not designed the tables in the best way possible, but right now tblZipCodes contains 46 fields (which are the names of the County) and the zip codes for each county listed below.



Currently I have a select query that selects WHERE tblZipCodes.['Field1'] = Left([tblContacts]![ZipCode],5) OR tblZipCodes.['Field2'] = Left([tblContacts]![ZipCode],5) OR etc. for all 46 fields. The resulting table is all fields being shown with the matching record value. Is it possible to have this just select from the field in tblZipCodes that the matching zip code is in? And is there a more different way for me to design the table/query?

I'm pretty new to access and all I can think of doing is running a nested For loop and returning the field name. Any help is appreciated! Thanks!

[CJ_London]

but right now tblZipCodes contains 46 fields (which are the names of the County) and the zip codes for each county listed below.

this sounds totally unnormalised - can you show an example of what this looks like. I would have expected two columns -zipcode and county.

[b_rye_chan]

Here's a small sample from tblZipCodes. Should I create a table per County instead? There are up to 525 records depending on the county

Los Angeles	Orange	San Diego	Ventura	Riverside	San Bernardino	Imperial
90001	90620	91901	91319	91752	91701	92222
90002	90621	91902	91320	92201	91708	92227
90003	90622	91903	91358	92202	91709	92231
90004	90623	91905	91359	92203	91710	92232
90005	90624	91906	91360	92210	91729	92233
90006	90630	91908	91361	92211	91730	92243
90007	90631	91909	91362	92220	91737	92244
90008	90632	91910	91377	92223	91739	92249
90009	90633	91911	93001	92225	91743	92250
90010	90680	91912	93002	92226	91758	92251
90011	90720	91913	93003	92230	91761	92257
90012	90721	91914	93004	92234	91762	92259
90013	90740	91915	93005	92235	91763	92266
90014	90742	91916	93006	92236	91764	92273
90015	90743	91917	93007	92239	91784	92275
90016	92602	91921	93009	92240	91785	92281
90017	92603	91931	93010	92241	91786	92283
90018	92604	91932	93011	92247	91798
90019	92605	91933	93012	92248	92242

[orange]

Did you consider just having the zipCode in the Contacts table, then join it to your ZipCodes table on ZipCode to show the County and Region?
No need to store the County and Region with Contact, unless there is more to your question than you have posted.

OOOOoops: I just saw your last post?????

I think you should describe what you are trying to accomplish in simple, plain English ---there may be options.

[b_rye_chan]

O wait, I think I see what you are saying...One field containing all the zip codes and one field with the corresponding County

[b_rye_chan]

So I'm trying to auto-populate the County and Region fields in tblContacts based on the Zip Code field.

[CJ_London]

as I thought - you just need the one table - something like

tblZipCodes
zipcode primary key (indexed, no duplicates)
County text (probably indexed, duplicates OK)

ZipCode..County
90001....Los Angeles
90002....Los Angeles
90620....Orange

Alternatively you may be you want a separate table for counties

tblCounties
CountyPK autonumber
CountyName text

to use as a lookup to ensure accurate spelling

[b_rye_chan]

Works perfectly now. Thanks a bunch! I figured I was overthinking it