I am making an append query to update the name of the relationship manager in my notes table based on the relationship manager in an annual review table. The tables are related by annual review number. When I use the append query to insert the relationship manager into the notes table it does just that - but the fields where it once was blank are still in the table. So I have doubled the number of records in the table. Now I have the original records with the blank relationship manager, plus the record with the relationship manager field now updated. Is there a way to do this without doubling the records?
INSERT INTO [Note Table] ( [Relationship Manager] )
SELECT [Note Table].[Relationship Manager]
FROM [Annual Review Table] INNER JOIN [Note Table] ON [Annual Review Table].[Annual Review Number] = [Note Table].[Annual Review Number];