Update table1 based on a match to table2

[SageOne]

Hi,

How do I update a field in a table in Access based on a "not null" result of
a join to another table.

For example, I would like a field in table1 to update to "yes" when the join
to table2 is not null. Normally, I would just use a select query with an IFF
function but in this instance I am trying to update a static table.

 

[John W. Vinson]

Hi,

How do I update a field in a table in Access based on a "not null" result of
a join to another table.

For example, I would like a field in table1 to update to "yes" when the join
to table2 is not null. Normally, I would just use a select query with an IFF
function but in this instance I am trying to update a static table.

Create a join in an Update query. No IIF is needed:

UPDATE firsttable INNER JOIN seconttable
ON firsttable.joinfield = secondtable.joinfield
SET somefield = "yes";

will update only those records which have a match.

For a more specific answer please post a more specific question.

 

[tasha]

Mr. Vinson:

Is it possible to do this also with a field? (See "combo box/relationship
help" for more background info)

 

[John W. Vinson]

Mr. Vinson:

Is it possible to do this also with a field? (See "combo box/relationship
help" for more background info)

I'm not at all sure what you mean. You can only update a table by updating its
fields. Could you explain (in this thread so I don't have to do a Google
Groups search for the other thread)?