updating muliple rows in one table based on information in another

I have 2 access tables...table 1 contains a street name and a low number and
a high number as well as a route number. I wish to update table 2 which has
a row for each address. If the street matches and the address is within the
range, I want to update the route number in table 2. There are multiple rows
in table 1 for each street name.

Both tables are quite - table 1 has 18,000 rows and table 2 has 500,000
rows.

Any suggestions would be greatly appreciated.

Al,
Please give some sample/example values from each table, with field
names.
"This is what I have... vs... this is what I want."
Explain the logic you would use if you were to manually update the
second, from the first.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"Al" wrote in message
...
I have 2 access tables...table 1 contains a street name and a low number
and
a high number as well as a route number. I wish to update table 2 which
has
a row for each address. If the street matches and the address is within
the
range, I want to update the route number in table 2. There are multiple
rows
in table 1 for each street name.

Both tables are quite - table 1 has 18,000 rows and table 2 has 500,000
rows.

Any suggestions would be greatly appreciated.

On Thu, 4 Mar 2010 10:39:02 -0800, Al wrote:

I have 2 access tables...table 1 contains a street name and a low number and
a high number as well as a route number. I wish to update table 2 which has
a row for each address. If the street matches and the address is within the
range, I want to update the route number in table 2. There are multiple rows
in table 1 for each street name.

Both tables are quite - table 1 has 18,000 rows and table 2 has 500,000
rows.

Any suggestions would be greatly appreciated.

Making some assumptions about the fieldnames, I'd suggest trying a "non equi
join" query. This must be built in SQL, the query grid can't do it as a join:

UPDATE Table2
INNER JOIN Table1
ON Table1.Street = Table2.Street
AND Table1.LowNum = Table2.Address
AND Table1.HighNum = Table2.Address
SET Table2.Route = Table1.Route;

It's possible that the join won't be updateable; in that case you may need a
(less efficient) subquery:

UPDATE Table2
SET Route = (SELECT Table1.Route FROM Table1 WHERE Table1.Street =
Table2.Street AND Table1.LowNum = Table2.Address
AND Table1.HighNum = Table2.Address);

One question: street names aren't unique across jurisdictions. There's a
Franklin Road in Caldwell ID and a (different!!) Franklin Road in Nampa ID;
they're about five miles apart. Can you be sure that you don't have
ambiguities, where there are two different locations both identified as "123
Main St."?
--

John W. Vinson [MVP]

John,

Your suggestion has gotten me on track...thanks for the help.

"John W. Vinson" wrote:

On Thu, 4 Mar 2010 10:39:02 -0800, Al wrote:

I have 2 access tables...table 1 contains a street name and a low number and
a high number as well as a route number. I wish to update table 2 which has
a row for each address. If the street matches and the address is within the
range, I want to update the route number in table 2. There are multiple rows
in table 1 for each street name.

Both tables are quite - table 1 has 18,000 rows and table 2 has 500,000
rows.

Any suggestions would be greatly appreciated.

Making some assumptions about the fieldnames, I'd suggest trying a "non equi
join" query. This must be built in SQL, the query grid can't do it as a join:

UPDATE Table2
INNER JOIN Table1
ON Table1.Street = Table2.Street
AND Table1.LowNum = Table2.Address
AND Table1.HighNum = Table2.Address
SET Table2.Route = Table1.Route;

It's possible that the join won't be updateable; in that case you may need a
(less efficient) subquery:

UPDATE Table2
SET Route = (SELECT Table1.Route FROM Table1 WHERE Table1.Street =
Table2.Street AND Table1.LowNum = Table2.Address
AND Table1.HighNum = Table2.Address);

One question: street names aren't unique across jurisdictions. There's a
Franklin Road in Caldwell ID and a (different!!) Franklin Road in Nampa ID;
they're about five miles apart. Can you be sure that you don't have
ambiguities, where there are two different locations both identified as "123
Main St."?
--

John W. Vinson [MVP]
.

Hi Al,

Thanks for you assistance...the reply from John W. Vinson has set me on the
right track.



"Al Campagna" wrote:

Al,
Please give some sample/example values from each table, with field
names.
"This is what I have... vs... this is what I want."
Explain the logic you would use if you were to manually update the
second, from the first.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"Al" wrote in message
...
I have 2 access tables...table 1 contains a street name and a low number
and
a high number as well as a route number. I wish to update table 2 which
has
a row for each address. If the street matches and the address is within
the
range, I want to update the route number in table 2. There are multiple
rows
in table 1 for each street name.

Both tables are quite - table 1 has 18,000 rows and table 2 has 500,000
rows.

Any suggestions would be greatly appreciated.


.

"Al" wrote in message
...
John,

Your suggestion has gotten me on track...thanks for the help.

"John W. Vinson" wrote:

On Thu, 4 Mar 2010 10:39:02 -0800, Al
wrote:

I have 2 access tables...table 1 contains a street name and a low number
and
a high number as well as a route number. I wish to update table 2 which
has
a row for each address. If the street matches and the address is within
the
range, I want to update the route number in table 2. There are multiple
rows
in table 1 for each street name.

Both tables are quite - table 1 has 18,000 rows and table 2 has
500,000
rows.

Any suggestions would be greatly appreciated.

Making some assumptions about the fieldnames, I'd suggest trying a "non
equi
join" query. This must be built in SQL, the query grid can't do it as a
join:

UPDATE Table2
INNER JOIN Table1
ON Table1.Street = Table2.Street
AND Table1.LowNum = Table2.Address
AND Table1.HighNum = Table2.Address
SET Table2.Route = Table1.Route;

It's possible that the join won't be updateable; in that case you may
need a
(less efficient) subquery:

UPDATE Table2
SET Route = (SELECT Table1.Route FROM Table1 WHERE Table1.Street =
Table2.Street AND Table1.LowNum = Table2.Address
AND Table1.HighNum = Table2.Address);

One question: street names aren't unique across jurisdictions. There's a
Franklin Road in Caldwell ID and a (different!!) Franklin Road in Nampa
ID;
they're about five miles apart. Can you be sure that you don't have
ambiguities, where there are two different locations both identified as
"123
Main St."?
--

John W. Vinson [MVP]
.

"Al" wrote in message
...
I have 2 access tables...table 1 contains a street name and a low number
and
a high number as well as a route number. I wish to update table 2 which
has
a row for each address. If the street matches and the address is within
the
range, I want to update the route number in table 2. There are multiple
rows
in table 1 for each street name.

Both tables are quite - table 1 has 18,000 rows and table 2 has 500,000
rows.

Any suggestions would be greatly appreciated.

y,,;;::!!!m!§!

"r lonergan" a écrit dans le message de groupe de
discussion : ...


"Al" wrote in message
...
John,

Your suggestion has gotten me on track...thanks for the help.

"John W. Vinson" wrote:

On Thu, 4 Mar 2010 10:39:02 -0800, Al
wrote:

I have 2 access tables...table 1 contains a street name and a low
number and
a high number as well as a route number. I wish to update table 2
which has
a row for each address. If the street matches and the address is
within the
range, I want to update the route number in table 2. There are multiple
rows
in table 1 for each street name.

Both tables are quite - table 1 has 18,000 rows and table 2 has
500,000
rows.

Any suggestions would be greatly appreciated.

Making some assumptions about the fieldnames, I'd suggest trying a "non
equi
join" query. This must be built in SQL, the query grid can't do it as a
join:

UPDATE Table2
INNER JOIN Table1
ON Table1.Street = Table2.Street
AND Table1.LowNum = Table2.Address
AND Table1.HighNum = Table2.Address
SET Table2.Route = Table1.Route;

It's possible that the join won't be updateable; in that case you may
need a
(less efficient) subquery:

UPDATE Table2
SET Route = (SELECT Table1.Route FROM Table1 WHERE Table1.Street =
Table2.Street AND Table1.LowNum = Table2.Address
AND Table1.HighNum = Table2.Address);

One question: street names aren't unique across jurisdictions. There's a
Franklin Road in Caldwell ID and a (different!!) Franklin Road in Nampa
ID;
they're about five miles apart. Can you be sure that you don't have
ambiguities, where there are two different locations both identified as
"123
Main St."?
--

John W. Vinson [MVP]
.