Updating fields in second table with calculated values from first tabl

[accessgoat]

Hi

I have 3 tables linked by ID. I need to update Table 3 with the calculated value from Table 2. When I run the query though it asks me to enter parameter value for the fields in Table 2. This is my query:

UPDATE tbl3 SET tbl3.[Ab1Days] = ((tbl2.SurgStDt)-(tbl2.Ab1Dt))
WHERE (tbl2.SurgStDt Is Not Null And tbl2.Abx1Dt Is Not Null);

The fields SurgStDt and Abx1Dt are stored in table 2 only. I need to update the field Ab1Days in Table 3. Can anyone suggest a way?

Thanks
accessgoat

 

[Golom]

You haven't told it how to relate tbl3 to tbl2. You probably need something like

UPDATE tbl3 INNER JOIN tbl2 ON tbl3.CommonField = tbl2.CommonField ...

 

[accessgoat]

Thanks much. I tried it and it works. My next question is I need to do 4 types of update calculation queries like that. For example one type of update query will calculate the timings for 40 fields. I was wondering if there is an easier way to do this instead of writing 40 queries, one for each field. (Multiply this by 4 and I will have to actually write 160 queries in all)

Appreciate it a lot.
accessgoat (ponytails2).

 

[lespaul]

you can add all the fields to be updated like below:

UPDATE tbl3 INNER JOIN tbl2 ON tbl3.CommonField = tbl2.CommonField SET tbl3.[Ab1Days] = ((tbl2.SurgStDt)-(tbl2.Ab1Dt)), tblname.fieldname = something, tblname.adifferentfield = somethingelse, tblname.anotherfield = anotherthing
WHERE (tbl2.SurgStDt Is Not Null And tbl2.Abx1Dt Is Not Null);

 

[accessgoat]

Hurrahhh! It worked. Thanks for saving me a bunch of work.

accessgoat