copying data from one table to another

[bobp]

Please can someone help me with this question.
I have a table that I wish to update with information from another. Two fields are common to both tables. I want to check one field for matching data and where there is a match to update the second field

 

[RainLover]

I have asked the Moderators to move this to a better Forum.

This Forum is just to say Hi and to say a little about yourself.

 

[RainLover]

About you question.

It is not normal to store the same Data in two places. This is one of the main features of a Database unlike a Spreadsheet.

So please explain a little what you really want to do. I am sure someone can supply a solution.

 

[bobp]

OK, my apologies if I didn't express myself clearly, I am new to this.
I am using an Access 2010 database. I have a main table with 10 fields in it. It is a table for storing information on chemicals and I have 2000 chemical records in it. Two of the columns (fields, that is right isn't it) contain identity numbers (called CAS No and EC No) but there are many absent entries in both columns. Let's call the columns A and B, respectively.
The second table is a chemical inventory which I have downloaded from the web. It contains many more records than I have but only the two columns A (CAS No) and B (EC No). I want this second table to search the entries in column A of the first table and where there is a match for the corresponding information column B to be added to column B in my table.

I assume I will use a query for this and then a make table command at the end but I don't how to get the information added to the main (first table).

Any help you can give will be very much appreciated.

Regards. Bob.

 

[RainLover]

Make a Select Query. One that displays all the correct data that you want to add to the other Table.

Then change the type of query to an "Append Query". It should ask you where you want to append the Records. You select the Destion Table. Then in the Row Append To Select the destination Field.

Save and run.

Make sure you have a Back up of the Database before doing this.

I Run 2003 so some of my instructions could be slightly different to 2010.

The is a Different method where you can use a Combo Box to select just One Record to be added to the Destination Table if this is what you want. It is called a LookUp.

Please post back for clarification if needed.

 

[bobp]

Thank you but I'm afraid I must be doing something wrong. Using the instructions the command added all the information in one column but that is not what I want. I want the program to scan the data in the correct column of the source table and only where it is already in my destination column to paste the corresponding data across to the second column. At the moment, I just got a lot of information in one column but nothing matching in the second column.
Please let me know what i am doing wrong.

 

[Pat Hartman]

You will need to do two separate update queries. This is not actual SQL. It is only air code for the concept.

Query1
Update tblA Set tblA.fld2 = tblB.fld2
Inner Join tblA to tblB on tblA.fld1 = tblB.fld1
Query2
Update tblA Set tblA.fld1 = tblB.fld1
Inner Join tblA to tblB on tblA.fld2 = tblB.fld2

 

[bobp]

Pat, thank you. I won't pursue this further. I know you have tried to help but I hjave not the slightest knowledge of how to use SQL in ACCESS and what you have provided is way above my head. I really don't understand. I will try and find someone who can work me through this, hands on, in a way that I understand.

 

[Pat Hartman]

Don't give up yet. This site has some tutorials. Try watching some on query building.