Solved

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

Posted on 2014-01-05
7
1,492 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
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 17

Accepted Solution

by:
John Gates earned 500 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 35

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 35

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

809 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