calculating using data from different DB

[kivenage]

i want to update a field "Target.Performance" from one database with the total sum of these fields "Contacts.Time + Contacts.IntTime + Contacts.TravelTime" from a different database. i have the Contacts table linked to the database with the Target table

here's my statement:

UPDATE Target SET Target.Performance = SW_Contacts.Time+SW_Contacts.InterpreterTime+SW_TravelTime+SW_PrepTime
WHERE ((([SW_Contacts].[StaffID])=[Target].[StaffID]));

but it asks for parameters for the time fields and staffid everytime i run the query. how do i get the statement to get the values of time already in the database, add them up and update 'Performance' from the other database?

thanks in advance.

 

[Jon K]

you need to join the tables with an Inner Join instead of using a Where clause:-

UPDATE Target INNER JOIN SW_Contacts ON Target.StaffID = SW_Contacts.StaffID
SET Target.Performance = SW_Contacts.Time + SW_Contacts.InterpreterTime + ... etc.


If there are Null values, use the nz() function in the sum as well:-

= nz(SW_Contacts.Time) + nz(SW_Contacts.InterpreterTime) + ... etc.