access update query

[arvarunar]

I have two tables:

TrendMaster: EmployeeID, Joined_Period, Delay, Index, Branch, K

*Joined_period takes month eg: "April", "March"...

TrendSheet: Branch, April_Joined, April_Delay, April_Index, May_Joined...so on till March_Delay, March_Index

I need to update April_Index with: Average of K factor of employees in TrendMaster for whome Joined_period is April-09 and these need to be grouped as TrendMaster.Branch=TrendSheet.Branch

This query works if i have Ahmedabad in TrendMaster. If I dont have it, this does not update 0. I want it to update 0 if there is no record found. Please Suggest

UPDATE TrendSheet INNER JOIN TrendMaster ON TrendSheet.Branch=TrendMaster.Branch SET April_Index = NZ(DAvg("[K]","TrendMaster","[Joined_Period]='April-09' AND [Branch]='Ahmedabad'"),0)
WHERE TrendSheet.Branch='Ahmedabad';

[ChipR]

An INNER JOIN is going to update only those records WHERE TrendShet.Branch = 'Ahmedabad', and TrendSheet.Branch = TrendMaster.Branch. Perhaps you want a different type of JOIN?

[arvarunar]

thanx! Left Outer Join works