Update Yes/No field over one to many relationship

[EmilyMoon]

Hi,

I have a database with two tables joined by a one to many relationship ("Master" to "Nested"). I have a yes/no column in each table.


I would like to use an update query to update yes/no records in the master table using yes/no records in the nested table. However as there are multiple records in the nested table, the method I'm currently using only updates the master table based on the last listed yes/no result in the nested table for each record.

What I would like it to do is to update the master table yes/no column to yes if ANY of the associated nested table records are yes...

Any advice greatly appreciated

Emily

[aytee111]

UPDATE testN INNER JOIN testM ON testN.keyfield = testM.keyfield SET testM.[yesno] = True
WHERE (((testN.check)=True));

(testN is the nested table, testM is the master)

[jzwp11]

If you base your decision on the child records why bother updating the parent record at all? There would be no need to do that. You can just query the child records and if there is at least 1 yes then you know the status of the parent record.

Not knowing your table structure it is hard to give you specifics but if it looks something like this...

tblParent
-pkParentID primary key, autonumber

tblChild
-pkChildID primary key, autonumber
-fkParentID foreign key to tblParent
-youryesnofield

...then you will need a series of queries to get the information you need.

This first query returns all the records that have yes responses

query name: qryYesResponsesbyfkParentID
SELECT tblChild.fkParentID, tblChild.youryesnofield
FROM tblChild
WHERE (((tblChild.youryesnofield)=True));

We can modify the above query to return 1 record for each fkParentID with a yes by adding DISTINCT to the SELECT clause.

SELECT DISTINCT tblChild.fkParentID, tblChild.youryesnofield
FROM tblChild
WHERE (((tblChild.youryesnofield)=True));


The above query will now tell us which parents had at least 1 yes and there is no need to even have the yes/no field in the parent table.