Automatically set a field based on a field in another table

[JVermast]

I have two tables, each has a "status" for a project. In the first table there is only one instance of each project name, in the second table there may be more than 1, will always have the same name but may have a different "Status" (field).

I need the Status field of the first table to be set to "Assigned" if ANY of the records in the second table pertaining to that project are set to "Assigned".

Can I do this with my table or will I need to do it with a query/form combination?

Thanks,

 

[JVermast]

To add, I know this is going to include some sort of if-statement, I just don't know the extent of it or how much it will be.

 

[JVermast]

Ok I have looked into this more and I understand I will need to use an "Update" query. I have tried to set it up but cannot seem to get my criteria right.

 

[neileg]

What possible values are there for the status field? If there are only two values, it would be better to use a numeric flag here. If you use '1' instead of 'Assigned' and '0' instead of 'Not Assigned' you can simply sum this field. If the sum is not equal to zero, you know at least one record is set to 'Assigned'.

 

[JVermast]

The status field has 4 possibilities on one side, and 2 on the other. In 2 instances it will be one status on the left, in 2 instances it will be another status on the left.

Right now I setup this Update Query just to do 1 of the status' to get it working:

8 is the id for the status "Assigned" in the SetStatus table
2 is the id for the status "Assigned" in the Status table.