Stumped on update query between tables with no Join fields

[KC-Mass]

I have two tables related to sales transactions and assigned areas.

The tblSalesArea has SalesAreaID an autonumber
StartZip, Text
EndZip, Text

The tblSales has many fields detailing all the sales. The two fields of
concern are
ZipCode, Text and SalesAreaID a number.

I need to populate the tblSales!SalesAreaID with the
tbleSalesArea!SalesAreaID
based on the zipcode being between tblSalesArea!startzip and
tblSalesArea!endzip.

This is where I am and it doesn't work. I suspect because the tables are
not joined.
I could open two datasets and do it pretty easily in code but that would be
slow.
Any Ideas??

Thx

UPDATE tblCentral, tblSalesAreas SET tblCentral.OwnerID =
tblSalesAreas!CompanyID
WHERE (((tblCentral.SoldToZip)>=[tblSalesAreas]![StartZip] And
(tblCentral.SoldToZip)
<=[tblSalesAreas]![EndZip]));

[rkc]

KC-Mass wrote:

I have two tables related to sales transactions and assigned areas.

The tblSalesArea has SalesAreaID an autonumber
StartZip, Text
EndZip, Text

The tblSales has many fields detailing all the sales. The two fields of
concern are
ZipCode, Text and SalesAreaID a number.

I need to populate the tblSales!SalesAreaID with the
tbleSalesArea!SalesAreaID
based on the zipcode being between tblSalesArea!startzip and
tblSalesArea!endzip.

This is where I am and it doesn't work. I suspect because the tables are
not joined.
I could open two datasets and do it pretty easily in code but that would be
slow.
Any Ideas??

Thx

UPDATE tblCentral, tblSalesAreas SET tblCentral.OwnerID =
tblSalesAreas!CompanyID
WHERE (((tblCentral.SoldToZip)>=[tblSalesAreas]![StartZip] And
(tblCentral.SoldToZip)
<=[tblSalesAreas]![EndZip]));

A much better solution would be to design your tables correctly.
Are you familiar with the concept of normalization?

You have a one to many relationship between SalesAreas and zipcodes
so there should be two tables to model that. The range thing is a real
bad idea.

Your Sales table has a Zipcode field so it does not need a SalesAreaID
field. That information would already be available from the first two
tables mentioned.

Don't worry though, someone will write you a query to work with the
mess you have.

[KC-Mass]

These are not really tables that have been designed in a DB. They are the
intial tables
created from reading in two distinct spreadsheets. I need to get that id
over so that I can
matchup the data in a real db.

But your comments are correct.
"rkc" <rk*@rkcny.yabba.dabba.do.comwrote in message
news:48**********************@roadrunner.com...

KC-Mass wrote:

>I have two tables related to sales transactions and assigned areas.

The tblSalesArea has SalesAreaID an autonumber
StartZip, Text
EndZip, Text

The tblSales has many fields detailing all the sales. The two fields of
concern are
ZipCode, Text and SalesAreaID a number.

I need to populate the tblSales!SalesAreaID with the
tbleSalesArea!SalesAreaID
based on the zipcode being between tblSalesArea!startzip and
tblSalesArea!endzip.

This is where I am and it doesn't work. I suspect because the tables are
not joined.
I could open two datasets and do it pretty easily in code but that would
be slow.
Any Ideas??

Thx

UPDATE tblCentral, tblSalesAreas SET tblCentral.OwnerID =
tblSalesAreas!CompanyID
WHERE (((tblCentral.SoldToZip)>=[tblSalesAreas]![StartZip] And
(tblCentral.SoldToZip)
<=[tblSalesAreas]![EndZip]));

A much better solution would be to design your tables correctly.
Are you familiar with the concept of normalization?

You have a one to many relationship between SalesAreas and zipcodes
so there should be two tables to model that. The range thing is a real
bad idea.

Your Sales table has a Zipcode field so it does not need a SalesAreaID
field. That information would already be available from the first two
tables mentioned.

Don't worry though, someone will write you a query to work with the
mess you have.

[rkc]

KC-Mass wrote:

These are not really tables that have been designed in a DB. They are the
intial tables
created from reading in two distinct spreadsheets. I need to get that id
over so that I can
matchup the data in a real db.

But your comments are correct.

Try replacing the bangs you have in your query with dots.
Perhaps use between as the comparison.

You're confusing referencing controls on a form with referencing
fields in a table.

[Bob Quintal]

"KC-Mass" <connearneyATcomcastDOTnetwrote in
news:EN******************************@comcast.com:

I have two tables related to sales transactions and assigned
areas.

The tblSalesArea has SalesAreaID an autonumber
StartZip, Text
EndZip, Text

The tblSales has many fields detailing all the sales. The two
fields of concern are
ZipCode, Text and SalesAreaID a number.

I need to populate the tblSales!SalesAreaID with the
tbleSalesArea!SalesAreaID
based on the zipcode being between tblSalesArea!startzip and
tblSalesArea!endzip.

This is where I am and it doesn't work. I suspect because the
tables are not joined.
I could open two datasets and do it pretty easily in code but that
would be slow.
Any Ideas??

Thx

UPDATE tblCentral, tblSalesAreas SET tblCentral.OwnerID =
tblSalesAreas!CompanyID
WHERE (((tblCentral.SoldToZip)>=[tblSalesAreas]![StartZip] And
(tblCentral.SoldToZip)
<=[tblSalesAreas]![EndZip]));

UPDATE tblCentral
SET tblCentral.OwnerID =
Dlookup("companyID","tblSalesAreas",
"SoldToZip between [StartZip] And [EndZip]");
--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[KC-Mass]

Thanks very much.

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.175.223.2...

"KC-Mass" <connearneyATcomcastDOTnetwrote in
news:EN******************************@comcast.com:

>I have two tables related to sales transactions and assigned
areas.

The tblSalesArea has SalesAreaID an autonumber
StartZip, Text
EndZip, Text

The tblSales has many fields detailing all the sales. The two
fields of concern are
ZipCode, Text and SalesAreaID a number.

I need to populate the tblSales!SalesAreaID with the
tbleSalesArea!SalesAreaID
based on the zipcode being between tblSalesArea!startzip and
tblSalesArea!endzip.

This is where I am and it doesn't work. I suspect because the
tables are not joined.
I could open two datasets and do it pretty easily in code but that
would be slow.
Any Ideas??

Thx

UPDATE tblCentral, tblSalesAreas SET tblCentral.OwnerID =
tblSalesAreas!CompanyID
WHERE (((tblCentral.SoldToZip)>=[tblSalesAreas]![StartZip] And
(tblCentral.SoldToZip)
<=[tblSalesAreas]![EndZip]));

UPDATE tblCentral
SET tblCentral.OwnerID =
Dlookup("companyID","tblSalesAreas",
"SoldToZip between [StartZip] And [EndZip]");
--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[Bob Quintal]

"KC-Mass" <connearneyATcomcastDOTnetwrote in
news:fO******************************@comcast.com:

Thanks very much.

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.175.223.2...

>"KC-Mass" <connearneyATcomcastDOTnetwrote in
news:EN******************************@comcast.com :

>>I have two tables related to sales transactions and assigned
areas.

The tblSalesArea has SalesAreaID an autonumber
StartZip, Text
EndZip, Text

The tblSales has many fields detailing all the sales. The two
fields of concern are
ZipCode, Text and SalesAreaID a number.

I need to populate the tblSales!SalesAreaID with the
tbleSalesArea!SalesAreaID
based on the zipcode being between tblSalesArea!startzip and
tblSalesArea!endzip.

This is where I am and it doesn't work. I suspect because the
tables are not joined.
I could open two datasets and do it pretty easily in code but
that would be slow.
Any Ideas??

Thx

UPDATE tblCentral, tblSalesAreas SET tblCentral.OwnerID =
tblSalesAreas!CompanyID
WHERE (((tblCentral.SoldToZip)>=[tblSalesAreas]![StartZip] And
(tblCentral.SoldToZip)
<=[tblSalesAreas]![EndZip]));

UPDATE tblCentral
SET tblCentral.OwnerID =
Dlookup("companyID","tblSalesAreas",
"SoldToZip between [StartZip] And [EndZip]");
--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

You may need to change the last line to
"SoldToZip between " & [StartZip] & " And " & [EndZip] );
or even
"SoldToZip between """ & [StartZip] & """ And """ & [EndZip] &
"""");

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[rkc]

Bob Quintal wrote:

"KC-Mass" <connearneyATcomcastDOTnetwrote in
news:fO******************************@comcast.com:

>Thanks very much.

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.175.223.2...

>>"KC-Mass" <connearneyATcomcastDOTnetwrote in
news:EN******************************@comcast.co m:

I have two tables related to sales transactions and assigned
areas.

The tblSalesArea has SalesAreaID an autonumber
StartZip, Text
EndZip, Text

The tblSales has many fields detailing all the sales. The two
fields of concern are
ZipCode, Text and SalesAreaID a number.

I need to populate the tblSales!SalesAreaID with the
tbleSalesArea!SalesAreaID
based on the zipcode being between tblSalesArea!startzip and
tblSalesArea!endzip.

This is where I am and it doesn't work. I suspect because the
tables are not joined.
I could open two datasets and do it pretty easily in code but
that would be slow.
Any Ideas??

Thx

UPDATE tblCentral, tblSalesAreas SET tblCentral.OwnerID =
tblSalesAreas!CompanyID
WHERE (((tblCentral.SoldToZip)>=[tblSalesAreas]![StartZip] And
(tblCentral.SoldToZip)
<=[tblSalesAreas]![EndZip]));

UPDATE tblCentral
SET tblCentral.OwnerID =
Dlookup("companyID","tblSalesAreas",
"SoldToZip between [StartZip] And [EndZip]");
--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

You may need to change the last line to
"SoldToZip between " & [StartZip] & " And " & [EndZip] );
or even
"SoldToZip between """ & [StartZip] & """ And """ & [EndZip] &
"""");

UPDATE tblCentral, tblSalesAreas
SET tblCentral.OwnerID = tblSalesAreasCompanyID
WHERE tblCentral.SoldToZip
BETWEEN tblSalesAreas.StartZip
And tblSalesAreas.EndZip