Update a field in a table based on two other fields

[Guest]

I have a sub table with a subform that has a new field (a text field). There
is an number field connected to another table that will have the same record
number listed a few times because it has multiple defect codes (the purpose
for the subform).

What I need is...if a user enters a "corrective action" [CorrAct] for a
particular defect code such as A01, then I need the rest of that defect code
for that ID number to be updated with the respective CorrAct text. But it
has to be only for that ID number and the specific defect code because there
will be different corrective actions for the specific defect code. I hope I
made sense...is there a way to do this with an update query?

 

[John W. Vinson]

I have a sub table with a subform that has a new field (a text field). There
is an number field connected to another table that will have the same record
number listed a few times because it has multiple defect codes (the purpose
for the subform).

What I need is...if a user enters a "corrective action" [CorrAct] for a
particular defect code such as A01, then I need the rest of that defect code
for that ID number to be updated with the respective CorrAct text. But it
has to be only for that ID number and the specific defect code because there
will be different corrective actions for the specific defect code. I hope I
made sense...is there a way to do this with an update query?

Well... it's possible, but it's also unnecessary and a bad idea.

You should NOT need to store the text redundantly in a second table. If you
have a table with three fields - DefectCode, ID, and CorrectiveAct, you can
*look up* the text any time.

John W. Vinson [MVP]

 

[Guest]

Maybe I didn't explain the problem carefully. I'm not the one who set up the
database this way, but I do see why they have a subform because there will be
many defect codes for one record. I now put a field in there to be
corrective action text for a particular defect. These defects are related to
the autonumber of the parent table. I want the Corrective action entered for
one type of defect, such as A01, for a given record number to be updated to
the rest of the defect codes for A01. I thought an update query might do the
trick. What would the SQL be for this.

I have a sub table with a subform that has a new field (a text field). There
is an number field connected to another table that will have the same record
number listed a few times because it has multiple defect codes (the purpose
for the subform).

What I need is...if a user enters a "corrective action" [CorrAct] for a
particular defect code such as A01, then I need the rest of that defect code
for that ID number to be updated with the respective CorrAct text. But it
has to be only for that ID number and the specific defect code because there
will be different corrective actions for the specific defect code. I hope I
made sense...is there a way to do this with an update query?

Well... it's possible, but it's also unnecessary and a bad idea.

You should NOT need to store the text redundantly in a second table. If you
have a table with three fields - DefectCode, ID, and CorrectiveAct, you can
*look up* the text any time.

John W. Vinson [MVP]