Address/Region Matching by ZIP Code Range

[Guest]

Hello,

I'm trying to figure out a way to populate an address table with the linking
ID of a region table. Here are the specifics:

tblLeads contains address information with a ZIP (ZIP Code) field and a
field for RegionID

tblRegions contains the fields of:
- StartZIP - start of the Left(ZIP,3) range.
- EndZIP - end of the Left(ZIP,3) range.
- RegionID - a unique ID assigned to each region

What I'm looking for is an update query for tblLeads that looks up the
RegionID value from Left([tblLeads].[ZIP],3) between StartZIP and EndZIP.

My goal is to then relate my tblLeads table back to the tblRegions table so
leads can be distributed to the appropriate people. Any suggestions?

Thanks -- dbqph

 

[Guest]

Try this ---
UPDATE tblRegions, tblLeads SET tblLeads.RegionID = [tblRegions].[RegionID]
WHERE (((Left([ZIP],3)) Between [StartZIP] And [EndZip]));

 

[Guest]

Karl,

Thank you. That got it (I was over-thinking the solution). I'll have to deal
with an issue where some of my ZIP codes are from Canada which resulted in a
data type mismatch error, but that should be easy enough to over come.

Thanks for the assistance.

dbqph

Try this ---
UPDATE tblRegions, tblLeads SET tblLeads.RegionID = [tblRegions].[RegionID]
WHERE (((Left([ZIP],3)) Between [StartZIP] And [EndZip]));

--
KARL DEWEY
Build a little - Test a little

Hello,

I'm trying to figure out a way to populate an address table with the linking
ID of a region table. Here are the specifics:

tblLeads contains address information with a ZIP (ZIP Code) field and a
field for RegionID

tblRegions contains the fields of:
- StartZIP - start of the Left(ZIP,3) range.
- EndZIP - end of the Left(ZIP,3) range.
- RegionID - a unique ID assigned to each region

What I'm looking for is an update query for tblLeads that looks up the
RegionID value from Left([tblLeads].[ZIP],3) between StartZIP and EndZIP.

My goal is to then relate my tblLeads table back to the tblRegions table so
leads can be distributed to the appropriate people. Any suggestions?

Thanks -- dbqph

 

[Guest]

Your Zip code field needs to be text and not number so there should be no
mismatch.
--
KARL DEWEY
Build a little - Test a little

Karl,

Thank you. That got it (I was over-thinking the solution). I'll have to deal
with an issue where some of my ZIP codes are from Canada which resulted in a
data type mismatch error, but that should be easy enough to over come.

Thanks for the assistance.

dbqph

Try this ---
UPDATE tblRegions, tblLeads SET tblLeads.RegionID = [tblRegions].[RegionID]
WHERE (((Left([ZIP],3)) Between [StartZIP] And [EndZip]));

--
KARL DEWEY
Build a little - Test a little

Hello,

I'm trying to figure out a way to populate an address table with the linking
ID of a region table. Here are the specifics:

tblLeads contains address information with a ZIP (ZIP Code) field and a
field for RegionID

tblRegions contains the fields of:
- StartZIP - start of the Left(ZIP,3) range.
- EndZIP - end of the Left(ZIP,3) range.
- RegionID - a unique ID assigned to each region

What I'm looking for is an update query for tblLeads that looks up the
RegionID value from Left([tblLeads].[ZIP],3) between StartZIP and EndZIP.

My goal is to then relate my tblLeads table back to the tblRegions table so
leads can be distributed to the appropriate people. Any suggestions?

Thanks -- dbqph