Update a table from another table

[path87]

Hi, I'm new in this forum. I need to resolve a problem with 2 tables in my db.
These 2 tables have the same column with the same labels. For example:

Table1	Table2
ID	ID
Index	Index
Product	Product
Description	Description

Table1 is in japanese and it receives the english translation with the update of its "Product" and "Description" columns.
Table2 is in french language.
Table1 receives also other updates, with new records added; Table2 not.
So:

• Table1 is in english with 1030 records, "ID" is the Primary Key (number), "Index" is the product code (number), "Product" is a text box with the english name of the product, "Description" is the english description of the product.
• Table2 is in french with 900 records, "ID" is the Primary Key (number), "Index" is the product code (number), "Product" is a text box with the french name of the product, "Description" is the french description of the product.

What I need to do:
I need to have a new table (or update Table1) with ID and INDEX columns from Table1; Product and Description columns from the Table2. Of course, if for that ID and that INDEX there is not a french record, it should have the Product and Description from Table1 (the english one). So I need to update in french language only the old records in the Table1.

I tried to use a LEFT JOIN, so I can see which record has not got the french translation, but I'm not able to realize what I need to do.

[NTC]

make a query joining the two tables so that the join has an arrow that points from Table1 to Table2.

Add all the fields of table 1 and just the Product, Description fields of table 2. Aliase the duplicate fields names to be: eProduct, eDescription and fProduct, fDescription You do that simply by manually inserting/putting "eProduct:" without the quotes - in front of the field name

when you run this some records will have data in all fields, some records will have data in just the table 1 fields

return to design view and create two new fields:
newProduct: iif(isnull(fProduct),eProduct,fProduct)
newDescription:iif(isnull(fDescription),eDescripti on,fDescription)

the iif is the key tool to understand; it is a straight forward if/then logic using commas - you can look up the documentation for that to know more about it....

[path87]

Thanks very much! I solved my problem.