update one table using lookup table

[simao]

--------------------------------------------------------------------------------

Hi there! I'm new to Access and need some help:

I need to update one table based on another (lookup table)

The first table contains the fields: asset_no; IP_address; Location.
The second table contains the fields IP_address; Location.

The IP address fields are truncated to reveal just the first three segments
of the IP address.

The first table contains a list of IP addresses with no locations. The second
table contains a list of all the possible truncated IP addresses and their corresponding
locations. I need to update the Locations for each record in the first table
by using the second table as a lookup table, identifying the matches in IP_address
then updating with the corresponding Location.

If this is a procedure in a module, that can be called and will modify the table. As the first table is constantly being updated, a simple procedure that I can rerun to perform this lookup/update on the first table would be good

 

[PHV]

Why not simply joining the tables (instead of storing derived values)?
SELECT A.asset_no, A.IP_address, B.Location
FROM [first table] AS A INNER JOIN [second table] AS B
ON A.IP_address = B.IP_address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886