?
Solved

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

Posted on 2014-01-05
7
Medium Priority
?
1,703 Views
Last Modified: 2014-01-20
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
0
Comment
Question by:daisypetals313
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39758201
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
 
LVL 18

Accepted Solution

by:
John Gates, CISSP earned 2000 total points
ID: 39758202
You can query with the left statement LEFT(<field name>, 5)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39758241
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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 38

Expert Comment

by:PatHartman
ID: 39759528
@IrogSinta,
For Jet/ACE unspecified joins result in Cartesian Products which are not updateable.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39759963
@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
 
LVL 38

Expert Comment

by:PatHartman
ID: 39760184
I guess that makes sense.
0
 

Author Closing Comment

by:daisypetals313
ID: 39795151
This one worked and was quick setup
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question