CODEWITHSUNDEEP
ms-access
Jesus Espiga
Jesus Espiga

Reputation: 69

Lookup Value in Access

I'm trying to do a lookup of a field form one table, to update values in another table. I know this can be done easily with a query, but is there a way to do it in a table?

Basically all I'm trying to do is an excel VLOOKUP but in Access. Where if I change the lookup value in my destination table, the value returned will be updated.

Upvotes: 0

Views: 934

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

You need to join the tables in a query and then set the values of a field in one table to the field in the second table based on the join fields (hope that made sense).

So, for example, if you have:
Table1 with KeyField1 and DescriptionField1
Table2 with KeyField2 and DescriptionField2

If you want to update DescriptionField1 with the values in DescriptionField2 where the KeyField values match you use this SQL: 

UPDATE Table1 INNER JOIN Table2 ON Table1.KeyField1 = Table2.KeyField2
SET Table1.DescriptionField1 = Table2.DescriptionField2

The other way is to use a look up field - select Lookup Wizard in the Data Type.
If taking this route I'd advise the ten commandments of Access tables :)

Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One. http://access.mvps.org/access/tencommandments.htm

Upvotes: 1

Related Questions