Insert text from TableA to TableB...

[mpuska]

Hi! How to insert different text into MS Access database (base.mdb) from TableA (Column2) into TableB (ColumnY), but only where the content in Column1 from TableA is equal to content of the ColumnX from TableB. Column1 in TableA and ColumnX in TableB are almost the same, but not equal, and I need to add data into TableB (ColumnY) from TableA (Column2) only for equal data in Table A and B. See examples for results I need (in red): TableA Column1 | Column2 ----------------- text1a | text2a text1b | text2b text1c | text2c text1d | text2d text1e | text2e TableB ColumnX | ColumnY ----------------- textX1 | textX2 | text1b | text2b text1c | text2c textX3 | text1e | text2e

[justsomeguy]

SQL includes a SELECT INTO statement for that, but I'm not sure if Access supports that or not.

[mpuska]

Can you write for me SQL statement SELECT INTO and I will try in Access to do that. Or I have to update more then 4000 records one by one. Thanks!

[justsomeguy]

You can use SELECT INTO to create a new table:http://office.microsoft.com/en-us/access/HP010322661033.aspxOr you can use INSERT INTO with a SELECT query to insert into an existing table:http://office.microsoft.com/en-us/access/HP010322451033.aspx

[mpuska]

Thank you very much for your help with http://office.microsoft.com/en-us/access/HP010322451033.aspxThe problem for me is how to write a statement with CONDITION to copy new data from TableA (Column2) into TableB (ColumnY), ONLY WHERE the content in Column1 from TableA is equal to content of the ColumnX from TableB. How to write CONDITION where to execute INSERT INTO and where not. 2 tables are in the same database. Can you write for me a statement using names of tables and columns from my example? Thanks!

[justsomeguy]

First, are you trying to insert or update? Are you trying to add brand-new records to the second table, or are you trying to update records that are already there with more information?

[mpuska]

I am trying to insert new records into second table. New records in database (from 4000 up) are allredy added by users and I must insert records (new content into existing cels) into old part of datebase (from 1 to 4000 records).

[justsomeguy]

OK, so you have table 1 and table 2. Table 1 has 4000 rows in it, maybe table 2 has 2000 rows. After this completes you want table 2 to have 6000 rows, correct?

[mpuska]

Incorrect, table 1 (source of new data) has 4000 rows (has 2 columns - 1st column has 4000 data, same data as 1st column of table 2) and table 2 has 6000 rows (has more data and many rows are inserted by users, is more accurate of table 1, but with empty cells in column 2, cells to be updated with the content from source table, from column 2).I have to insert new data into table 2 (table with 6000 rows) into 2nd column, but only where data in 1st column of table 1 and table 2 are the same. Order of data in two tables (1st column) is not the same. Many data in table 2 are in last months deleted, changed and new data is added. Now I must add new data from table 1 (source) into table 2, where data in 1st columns is not changed or is not new, added by users.

[justsomeguy]

OK, then you are UPDATING existing rows, not INSERTING new rows. Insert means you add a brand new record to the database, update means you are changing the info in an existing record. This should help you:http://office.microsoft.com/en-us/access/HP051887101033.aspxhttp://www.google.com/search?hl=en&cli...amp;btnG=Search

[mpuska]

Problem solved!Solution for my problem was here:http://www.techonthenet.com/access/queries/update2_2007.phpThe SQL will update the MFG field in the Bot table with the value in the MFG field in the Big table when the PART values match:UPDATE Big INNER JOIN Bot ON Big.PART = Bot.PART SET Bot.MFG = [big].[MFG];Thanks for your help!