How to update table on query when the field is on another table?

[awinnn]

Hi,
I want to update table, [leave] with field carryfwd, annual and year. Every year, the data will be changed (except for annual field). My problem is to set new carryfwd value. This is because, the new carryfwd should be total(carryfwd + annual) - total(daystaken).

While table, [leavedetail] has leavecode, startdate, and enddate. Daystaken is difference of enddate and startdate.

Table [leave] and [leavedetail] is linked by another table, [staffdetail].

How do i include table [leavedetail] into this query?

UPDATE [leave] SET [leave].carryfwd = ([leave].[carryfwd]+[leave].[annual]), [leave].[year] = 2004
WHERE ((([leave].[year])=2003));

any idea?
thanx in advance..

 

[Elysium]

You could use a nested SQL statement in the query, provided that I am understanding you correctly.