Sql to add fields and the data from one table to another

[jerryczarnowski]

Hello,

I would like to create a query via SQL to add two fields and the data from table2 to table1. They both have ID as the primary key. Attached is the visual result.

Thanks, JCDFCM

 

[plog]

That doesn't make sense. The result is just Table2, why not just use that?

 

[isladogs]

Agree with plog
It isn't a good idea to duplicate data in more than one table.
You don't need table1.

 

[jerryczarnowski]

I may need to back up a bit. Table1 and Table2 are for example only. Table2 in reality came from an excel spreadsheet and has 5,640 rows and 22 columns. Table1 is a scrubbed version of table2 and was scaled down to the columns of importance. To make things worse, the person maintaining the spreadsheet no longer works with us. With the information I provided, one would think to just use Table2 and delete the unwanted columns however this cannot be done for one important reason: Each row in the Description field was modified in Table1 with the correct data...I apologize for not supplying all the details. The actual tables have proprietary info and cannot be shared. I am hoping for some trick Join or Union SQL syntax that could add the RPM and FEED columns to the scaled down and clean Table1. Both tables have ID as the primary key. Any help would be greatly appreciated.

Thanks, JCDFCM

 

[plog]

CAn you better demonstrate your issue with sample data?

 

[isladogs]

OK - if I understand you correctly the code below should do the job.
I recommend you BACKUP your data first just in case.

RPM is clearly an integer number field
Its not clear from your screenshots whether FEED is number or text datatype. It seems to have a decimal point

So I've given 3 alternatives below

Create a new module and add this procedure:

Option Compare Database
Option Explicit

Sub UpdateTable1()

[B][I][COLOR="SeaGreen"]'Add 2 fields RPM & FEED to Table1[/COLOR][/I][/B]

[COLOR="seagreen"][B][I]'If both are integer number fields use this[/I][/B][/COLOR]
CurrentDb.Execute "ALTER TABLE Table1 ADD COLUMN RPM INT, FEED INT;"

[COLOR="seagreen"][B][I]'if FEED is a number field with decimal places e.g. 16.35, remove the above and uncomment this line[/I][/B][/COLOR]
CurrentDb.Execute "ALTER TABLE Table1 ADD COLUMN RPM INT, FEED DOUBLE;"

[COLOR="seagreen"][B][I]'otherwise remove the above lines & uncomment the line below [/COLOR]
'this gives a field size of 10, adjust as appropriate[/I][/B]
'CurrentDb.Execute "ALTER TABLE Table1 ADD COLUMN RPM INT, FEED TEXT(10);"

[COLOR="seagreen"][B][I]'Now update the 2 new fields in Table1 from Table2[/I][/B][/COLOR]
CurrentDB.Execute "UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID" & _
	" SET Table1.RPM = [Table2].[RPM], Table1.FEED = [Table2].[FEED];"

End Sub

Save and run the above routine

 

[jerryczarnowski]

Your 3rd option gave me the idea below...both work.

SELECT Table1.ID, Table1.COLOR, Table1.TYPE, Table1.DESCRIPTION, Table2.RPM, Table2.FEED
FROM Table1 INNER JOIN Table2 ON Table1.[ID] = Table2.[ID];


Thanks!

 

[isladogs]

Well obviously you can do that but you said you wanted to copy the data to table1.

If you use my solution you can get rid of table2 and solve the duplication