Writing a query to link to just the first 5 digits of a zip code

I have a list of contacts with zip codes that are both 5 digit and 5 digit + 4.  

For example:  12345 and 12345-5555

This is shown in the attached file under the table "Master".

I have another table called "Zip Info" which ties in certain information based on zip code, such as Color and Area, however, this uses zips that are 5 digits only.

How would I write query so that the Zip code is truncated to the first 5 digits so that it can match up to the proper record in the "Zip Info" table.

A sample of the final desired version is shown in the table "FINAL SAMPLE".
Database1.accdb
daisypetals313Asked:
Who is Participating?
 
John Gates, CISSPConnect With a Mentor Security ProfessionalCommented:
You can query with the left statement LEFT(<field name>, 5)
0
 
mbizupCommented:
Give this a try:

SELECT Master.*, [Zip Info].*
FROM Master INNER JOIN [Zip Info] ON Left(Master.Zip, 5)  = Left([Zip Info].Zip,5)

Open in new window

0
 
IrogSintaCommented:
Here's one other way.  This allows you to still see the query in Design View.
SELECT Master.ID, Contact, Master.Zip, Color, Area
FROM Master, [Zip Info]
WHERE Left([Master].[Zip],5)=Left([Zip Info].[Zip],5)

Open in new window

Ron
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
PatHartmanCommented:
@IrogSinta,
For Jet/ACE unspecified joins result in Cartesian Products which are not updateable.
0
 
IrogSintaCommented:
@PatHartman,
I just tried using Inner Join and once you add the LEFT function, the query is not updateable either so I guess in this instance it doesn't matter.

Ron
0
 
PatHartmanCommented:
I guess that makes sense.
0
 
daisypetals313Author Commented:
This one worked and was quick setup
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.