Update Query - Transfer From Record to Record

[crhodus]

I'm trying to figure out how to transfer data from one record to another in my database.

I've got a table named tblCompany. My Primary Key is CompanyNumber. What I would like to do is have the user select a company and transfer all of this data, except the CompanyNumber, to another company that has a different CompanyNumber in the database.

Example: User selects company # 123. Then in some pop up window, the user types in company # 456 of another company that is in the database. All of the data from company # 123 ,except for the company number, would be put into company # 456.

Can anyone give me a short example on how I would write the query?

Thanks!

 

[Peter Whitfield]

Is it not a case of creating a select query to pull the relevant records based on company number. Then a second query with query1 as its data source which appends the relevant table with everything but the company number, the company number would be appended as the second pop up question.

Apologies, if this is utter rubbish but I dont have Access to hand to test it!

 

[Pat Hartman]

The query would look something like:

INSERT INTO tblCompany( CompanyNumber, AnotherField )
SELECT Forms!YourForm!YourNewCompanyNumber, A.AnotherField
FROM tblCompany AS A
WHERE A.CompanyNumber = Forms!YourForm!YourOldCompanyNumber;

Essentially what this does is use your old key value as the selection criteria in the WHERE clause and the new value in the SELECT clause.

 

[crhodus]

Thanks for your help. The query works great!