is it possible to use WHERE criteria stored in a table to make updates in another table in MS Access -

i figure it'll easier explain example. have 2 tables so.

+----+--------------------------------+---------+ |                    table 1                    | +----+--------------------------------+---------+ | id |            criteria            |  value  | +----+--------------------------------+---------+ |  1 | [table 2].[name] "dingo*" | alpha   | |  2 | [table 2].[location] = "here"  | bravo   | |  3 | [table 2].[active] = true      | charlie | +----+--------------------------------+---------+  +----+-------------+----------+--------+-------+ |                   table 2                    | +----+-------------+----------+--------+-------+ | id |    name     | location | active | value | +----+-------------+----------+--------+-------+ |  1 | dingo bingo | there    | false  |       | |  2 | bob         | here     | false  |       | |  3 | bingo       | there    | true   |       | |  4 | bingo bob   | here     | false  |       | +----+-------------+----------+--------+-------+ 

table 1 has rows of matching "criteria" table 2 , value row in table 2. know can use vba iterate through each row in table 1, create sql update string can used update table 2.

for example, sql first row in table 1 is:

update [table 2] set [table 2].[value] = "alpha" [table 2].[name] "dingo*" 

and result is:

+----+-------------+----------+--------+-------+ |                   table 2                    | +----+-------------+----------+--------+-------+ | id |    name     | location | active | value | +----+-------------+----------+--------+-------+ |  1 | dingo bingo | there    | false  | alpha | |  2 | bob         | here     | false  |       | |  3 | bingo       | there    | true   |       | |  4 | bingo bob   | here     | false  |       | +----+-------------+----------+--------+-------+ 

but i'm wondering if there way use single query instead of having loop through each row in table 1.

if matters, guaranteed criteria in table 1 have no overlaps. in other words, if id 1 of table 1 matches id 1 in table 2 no other rows in table 1 match id 1 in table 2.

no, impossible without vba.

allowing collapse space-time continuum or @ least result in lisp.

but possible without looping through every record manually.

write public function accepts sql fields parameters , uses them build , execute sql statement. i'm not sure it's advisable, it's unlikely collapse space-time continuum.

public function runtablesql(byval svalue string, byval scriteria string) long      dim ssql string      on error goto eh     ssql = "update [table 2] set [table 2].[value] = "" & svalue & "" _         & "where " & scriteria     currentdb.execute ssql             runtablesql = 0      exit function eh:     runtablesql = err.number     err.clear end function 

and run every row creating query this:

select *,runtablesql([value],[criteria]) result [table 1]