update yes / no field in table based on another table

[Bryan]

I am wanting to update a yes/no field in one table based on another table.

If the name is in both tables to put a "yes" value in the field
If the name is in one table but not the other to put a "no" value in the field

For example:

Table 1: Name
Table 2: Name
Check box = yes


Table 1: Null (Name not in table)
Table 2: Name
check box = no

 

[John W. Vinson]

I am wanting to update a yes/no field in one table based on another table.

If the name is in both tables to put a "yes" value in the field
If the name is in one table but not the other to put a "no" value in the field

For example:

Table 1: Name
Table 2: Name
Check box = yes


Table 1: Null (Name not in table)
Table 2: Name
check box = no

I would be very strongly inclined to say that this field *should not exist*.
Its value depends on the values of two other values, either of which could be
edited or deleted at any time.

You can instead determine it at any time in a Query joining the two tables, or
using DLookUp.

 

[Bryan]

The problem is that I need to update table 2 based on the names in table 1.
The yes/no value is linked to a delete query so that the names in each table
will alway match = if table 2 has a no value then that row is deleted from
the table.

 

[John W. Vinson]

The problem is that I need to update table 2 based on the names in table 1.
The yes/no value is linked to a delete query so that the names in each table
will alway match = if table 2 has a no value then that row is deleted from
the table.

No. You do NOT NEED the yes/no field in order to run the delete query.

You can base the Delete query on a Join, or use a Subquery to identifiy which
names exist in the other table.

To delete records in Table1 where the name value does not exist in Table2,

DELETE * FROM Table1
WHERE NOT EXISTS
(SELECT Table2.namefield FROM table2 WHERE table2.namefield =
table1.namefield);


I do hope these aren't people's names though - names are NOT unique, and you
might have two or more people named (say) Jim Smith.