ms access - Update field in one table based on values in another table

Question

I have a status field in a table in which test steps are marked as pass or fail and in another table I have to update test case(testID) as pass if all test step(testID) are pass, and fail test case if one of test is fail.

I have testID field common in both tables.

In the 1st table one column is testID and 5 steps are corresponding to that test ID and they can be pass or fail.

In the 2nd table I have one column in which I have to mark status as pass or fail based on overall 5 steps.

Table 1

Table 2

Answer 1

The following queries perform the update in two steps: the 1st query updates those tests which exist and pass, and the 2nd query updates those tests which exist and fail.

Update 'passed' tests:

update table1 t1
set t1.teststatus = 'pass'
where t1.testID in
(
    select t2.testID
    from table2 t2
    group by t2.testID
    having min(t2.status) = max(t2.status) and min(t2.status) = 'pass'
)

Update 'failed' tests:

update table1 t1
set t1.teststatus = 'fail'
where t1.testID in
(
    select t2.testID
    from table2 t2
    where t2.status = 'fail'
    group by t2.testID
)