By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Update Query Help

P: 76
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
Feb 15 '07 #1
Share this Question
Share on Google+
2 Replies


P: 49
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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblCopyTo
  2.            Set "FieldName of where you want the data to go" = [tblCopyFrom]!["FiledName fo where you are getting the data from"]
  3.            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.
Feb 15 '07 #2

ADezii
Expert 5K+
P: 8,597
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
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblCopyFrom INNER JOIN tblCopyTo
  2.     ON tblCopyFrom.PrimaryKey = tblCopyTo.ForeignKey
  3. SET tblCopyTo.SomeField = tblCopyFrom!SomeField;
Feb 15 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.