Clicky

I have 2 tables. The first contains potential customers with name, address and zip code fields and a SalesId field. The second table contains sales territories with SalesId, BeginningZip and EndingZip fields.

I need a query that will marry the 2 tables together and assign the SalesId value from the sales territories table to the customer's table where the zipcode falls in the beginning and ending zip ranges.  

asked 12/15/2011 02:11

dwcummings's gravatar image

dwcummings ♦♦


10 Answers:
After backing up your database, try:

UPDATE table1
SET SalesID = DLOOKUP("SalesID", "Table2", "[BeginningZip] <= '" & table1.ZipCode & "' AND [EndingZip] >= '" & table1.zipcode & "'")
link

answered

fyed's gravatar image

fyed

update customers c, territory t
set c.salesid=dlookup("salesID", "t","[c].[zip] >=  [t].[beginnningZip]  and  [c].[zip] <=[t].[endingzip])
link

answered 2011-12-15 at 10:15:36

capricorn1's gravatar image

capricorn1

Select * from customerTable a,salesTable b
where a.SalesId=b.SalesId and (a.zipcode>=b.BeginningZip and a.zipcode<=b.EndingZip);

Assumption:
Your current tables:
customerTable
salesTable
link

answered 2011-12-15 at 10:17:39

yjchong514's gravatar image

yjchong514

Thanks for getting back to me so quickly.

Fyed, I tried your suggestion, but it didn't work. It actually deleted values that already existed in the SalesId field.

Capricorn1, I get an error message when I attempted to use your suggestion, that being "Unknown".

Here is my SQL statement. Perhaps I entered something incorrectly.

UPDATE 16263A2 AS C, dbo_tblSalesTerritories AS T SET C.SalesId = DLookUp("SalesId","T","[C].[Czip] >= [T].[BeginningZip] and [C].[Czip] <= [T].[EndingZip]");
link

answered 2011-12-15 at 10:18:12

dwcummings's gravatar image

dwcummings


UPDATE [16263A2] AS C, [dbo_tblSalesTerritories] AS T SET C.SalesId = DLookUp("SalesId","T","[C].[Czip] >= [T].[BeginningZip] and [C].[Czip] <= [T].[EndingZip]");
link

answered 2011-12-15 at 12:23:43

capricorn1's gravatar image

capricorn1

Same result - "Unknown" error message.
link

answered 2011-12-15 at 12:28:35

dwcummings's gravatar image

dwcummings



UPDATE [16263A2] AS C, [dbo_tblSalesTerritories] AS T SET C.SalesId = " & DLookUp("SalesId","T","[C].[Czip] >= [T].[BeginningZip] and [C].[Czip] <= [T].[EndingZip]");
link

answered 2011-12-15 at 12:33:04

capricorn1's gravatar image

capricorn1

I get a syntax error message.
link

answered 2011-12-15 at 12:36:40

dwcummings's gravatar image

dwcummings

try this


UPDATE [16263A2] AS C, dbo_tblSalesTerritories AS T
SET C.SalesID = [T].[SalesId]
WHERE C.Czip>=[T].[BeginningZip] AND C.Czip<=[T].[EndingZip]
link

answered 2011-12-15 at 12:45:59

capricorn1's gravatar image

capricorn1

Capricorn1,

It worked like a charm. Thanks for the help.

Doug
link

answered 2011-12-15 at 14:05:50

dwcummings's gravatar image

dwcummings

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×14

Asked: 12/15/2011 02:11

Seen: 355 times

Last updated: 12/16/2011 01:03