Multi-Table Update Query or Lookup Problem

[SowBelly]

Hello:

I have 2 tables set up as such:

TABLE1
MAP# (Initially Blank)
ADDRESS1
ADDRESS2
STREET
Other misc fields also

TABLE2
MAP# (Has a map code)
ADDRESS1
ADDRESS2
STREET
Other misc fields also

I want to be able to update TABLE1.MAP# with TABLE2.MAP# if

TABLE1.ADDRESS1=TABLE2.ADDRESS1 and
TABLE1.ADDRESS2=TABLE2.ADDRESS2 and
TABLE1.STREET=TABLE2.STREET

TABLE1 and TABLE2 data is generated by 2 different systems.
Ultimately, I need to pass TABLE1 data back to its system after
updating the MAP# field.

I would prefer to do this without having to use VBA, but will if
necessary.

Thanx for any help you can provide.

"Pigs can't be humans, but humans can be Pigs!"

Eating at the trough of life.

Oink!

SowBelly

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've just about written your own solution.

Put this in an SQL view of a query definition:

UPDATE table1 T1 INNER JOIN table2 T2
ON T1.ADDRESS1=T2.ADDRESS1 and T1.ADDRESS2=T2.ADDRESS2 and
T1.STREET=T2.STREET
SET T1.[MAP#] = T2.[MAP#]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUDot4echKqOuFEgEQIJigCdFWj78KMUWeioSKBIZLzgnIdoFCwAnAnI
FcrJ6ev3TJ8cO2V77z79LudA
=dSB4
-----END PGP SIGNATURE-----

 

[SowBelly]

MG:

The solution worked. Thanx a lot.

"Pigs can't be humans, but humans can be Pigs!"

Eating at the trough of life.

Oink!

SowBelly