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!