Updating a yes/no column where data exists for that ID in another table?

[gh05]

I have two tables, both have a common ID field.

One table (the one that I want to update) has a yes/no field and I want it to say 'yes' if a record exists for that ID in the other table. If no record exists for that ID in the other table then it should say 'no'.

I'm guessin this should be simple but don't now how.



Any help much appreciated

[orange]

What have you tried? What error did you get?

[gh05]

What have you tried? What error did you get?

I haven't yet - If the table which I want to use to update the main table had yes/no in it as a column then I would just create a query to pull the two together where the ID is the same but because this second table only contains those which are 'yes' then I'm not sure of what to do.

[orange]

From a logical perspective, consider this.

Lets say TblA is the table with the Yes/No field (I 'll call it Chk, and in this example it is a Yes/No field (Boolean))
and your second table is TblB. TblA and TblB both have a field called Id.

TblA.Chk will be True/Yes if the Id is in TblB
TblA.Chk will be No/False if the Id is not in TblB

So,you know how to set the field to Yes/True where the Id is equal.

Well what if you first set all the Chk fields to NO/False,
then run the query you already know, to set the Chk to Yes/Ture where the Ids are equal.

That way you get the result you want.




The query you already know is

UPDATE TablA INNER JOIN TablB
ON TablA.id = TablB.id
SET TablA.Chk = True

So, the query you need to set all the values of Chk to No/False is

Update TblA
Set Chk = False

Make sense?

[gh05]

From a logical perspective, consider this.

Lets say TblA is the table with the Yes/No field (I 'll call it Chk, and in this example it is a Yes/No field (Boolean))
and your second table is TblB. TblA and TblB both have a field called Id.

TblA.Chk will be True/Yes if the Id is in TblB
TblA.Chk will be No/False if the Id is not in TblB

So,you know how to set the field to Yes/True where the Id is equal.

Well what if you first set all the Chk fields to NO/False,
then run the query you already know, to set the Chk to Yes/Ture where the Ids are equal.

That way you get the result you want.




The query you already know is



So, the query you need to set all the values of Chk to No/False is



Make sense?

Hi,

Thanks, yes the logic makes sense. I just wasn't sure how to do the query - so is this just a basic update query then? How do you 'set' the checkboxes without using the sql code?

thanks

[orange]

The sql code is what lies behind the Access Query Wizard (interface).

You should be familiar with the Query Wizard. You have probably used it for Select queries. If not, you should get familiar with it.

Also, you should be cautious when using Update queries. If the proper constraints are not used properly, you could modify ALL records, or the UNINTENDED records.

see http://www.techonthenet.com/sql/update.php

[Rhino373]

To set it, in the update query, you'll use 0 for No and 1 for Yes. Set the criteria to 1 to change the field to Yes

[gh05]

thanks both. sorted now.