Update Query

[InfoNow]

Have two tables (usystblEmployee and usystblTrainRec). When HR updates the usystblEmployee table, I would like to run a query to update the usystblTrainRec table to keep them in sync. I tried the update query posted below, but I am not getting any updated records. I do know for a fact that there changes in the usystblEmployee that the query should have caught. I am not getting any error message either. Please look at the SQL and show me the way. Thank you.

UPDATE UsystblTrainRec INNER JOIN UsystblEmployee ON UsystblTrainRec.EmpNum = UsystblEmployee.EmpNum SET UsystblTrainRec.EmpNum = UsystblEmployee.EmpNum, UsystblTrainRec.FName = UsystblEmployee.FName, UsystblTrainRec.LName = UsystblEmployee.LName, UsystblTrainRec.Dept = UsystblEmployee.Dept, UsystblTrainRec.CostCenter = UsystblEmployee.CostCenter, UsystblTrainRec.ReportTo = UsystblEmployee.ReportTo
WHERE ((((([UsystblTrainRec].[EmpNum])=[UsystblEmployee].[EmpNum] And [UsystblTrainRec].[FName]=[UsystblEmployee].[FName] And [UsystblTrainRec].[LName]=[UsystblEmployee].[LName] And [UsystblTrainRec].[CostCenter]=[UsystblEmployee].[CostCenter] And [UsystblTrainRec].[Dept]=[UsystblEmployee].[Dept] And [UsystblTrainRec].[ReportTo]=[UsystblEmployee].[ReportTo]))=False));

 

[lespaul]

Why would you want to duplicate all the data in UsystblEmployee? It appears that you have the fields EmpNum, LName, FName, Dept, CostCenter and ReportTo in both tables!

In a standard normalized database you would only store that information once in the UsystblEmployee and in the UsystblTrainRec would only have the employee's EmpNum and the TrainingID (probably a FK from a Training table?) and an indicator of whether the training has occurred.

Check out this link for more information about normalization:

http://www.alphabetcitydataworks.com/Developers/FundamentalsOfRelationalDatabaseDesign.doc

Leslie