Update Query Help

[jpatchak]

I want to write an update query that updates fields from one table to another. The first table (tblCopyFrom) is in a one-to-one relationship with the 2nd (tblCopyTo). The primary key in tblCopyFrom is stored in a foreign key field in tblCopyTo. I want the query to update tblCopyTo to values of the corresponding record in tblCopyFrom where the names of the fields in each table are the same. I know how to do this in VBA with ADODB recordsets, but I was wondering if there is a way to do it with a SQL statement. Any thoughts?

Thanks,
Josh

[tehgreatmg]

I want to write an update query that updates fields from one table to another. The first table (tblCopyFrom) is in a one-to-one relationship with the 2nd (tblCopyTo). The primary key in tblCopyFrom is stored in a foreign key field in tblCopyTo. I want the query to update tblCopyTo to values of the corresponding record in tblCopyFrom where the names of the fields in each table are the same. I know how to do this in VBA with ADODB recordsets, but I was wondering if there is a way to do it with a SQL statement. Any thoughts?

Thanks,
Josh

I am not quite sure on this but this may help you get started:

UPDATE tblCopyTo
           Set "FieldName of where you want the data to go" = [tblCopyFrom]!["FiledName fo where you are getting the data from"]
           Where "Foriegn Key Field" = [tblCopyFrom]!["Primary Key Field]

Where I have the quotes around things that is just to represent fields, you do not need the quotes. And like I said I am not quite sure if the syntax is right, it probably isn't, but it will help you get started.

[ADezii]

I want to write an update query that updates fields from one table to another. The first table (tblCopyFrom) is in a one-to-one relationship with the 2nd (tblCopyTo). The primary key in tblCopyFrom is stored in a foreign key field in tblCopyTo. I want the query to update tblCopyTo to values of the corresponding record in tblCopyFrom where the names of the fields in each table are the same. I know how to do this in VBA with ADODB recordsets, but I was wondering if there is a way to do it with a SQL statement. Any thoughts?

Thanks,
Josh

UPDATE tblCopyFrom INNER JOIN tblCopyTo
    ON tblCopyFrom.PrimaryKey = tblCopyTo.ForeignKey
SET tblCopyTo.SomeField = tblCopyFrom!SomeField;