Update question

  • Thread starter Thread starter Jimmy
  • Start date Start date
J

Jimmy

Hello. I have two different tables. Both of those tables have one field
called "codeID" which may have common data (may have unique data also).
Table1 has the fields (I'm renaming them since they are in Greek):
codeID
F1
F2
F3
Table2 has the fields:
codeID
F4
F5
F6
What I want is to add the data of Table2 to Table1 of those records in which
codeID has unique records.
I already added in Table1 the fields F4 F5 and F6 which are blank for now
and want to update them with the records of Table2, but as I mentioned only
of those records in which codeID does not already excist in Table1.
Or in other words add new records in table1 from table2 without creating
duplicate records in the field codeID.

I hope I make any sense, since my english isn't the best.
Please can you give me easy to follow instructions?

Thank You all
Jimmy
 
You should do this in 2 steps:

Step1 will only add the unique records from table2 to table1 with the
following statement:

INSERT INTO Table1 ( CodeId, f4, f5, f6 )
SELECT Table2.CodeId, Table2.f4, Table2.f5, Table2.f6
FROM Table2 LEFT JOIN Table1 ON Table2.CodeId = Table1.CodeId
WHERE (((Table1.CodeId) Is Null));

Step2 will update the existing records in table1 with the information of
table2:

UPDATE Table2 INNER JOIN Table1 ON Table2.CodeId = Table1.CodeId SET
Table2.f4 = [table1].[f4], Table2.f5 = [table1].[f5], Table2.f6 =
[table1].[f6];


- Raoul
 
It sounds to me that the O.P. only wants the first step (of your 2 steps)
which you have provided a suitable SQL.
 
Thank You for your replies.
Just one question.
Where exactly am I going to write that?
I am a little confused.
Sorry but I am new in access.
Thanks again.
Jimmy
 
When you create a new query then you can switch the view sql view.

Then just copy paste the sql statement and it should work.

- Raoul
 
Back
Top