Update of Table Problem

[Erictsang]

Dear all

I have some confusion on updating a table from EXCEL

i have import data from an excel field
ID
Fname
Lname
Tel

then i have another one to update to the above table
ID
Fname
Lname
Tel
Salary

Should i import it again and overwrite or Append?
I try both but not working




Wonder why

Eric

[ranman256]

the excel file should be linked in the db as an external table.
make 2 queries: ( to do both), append and update

1 make an append query to get items that do not exist.
in the query you need both the data table and the XL table,
join the 2 on ID,
dbl-click the join line, and make an OUTER join....all records in access table, some recs in XL table.
bring down the ID of both tables,
under the XL.ID set criteria to NULL, (this will only allow MISSING xl records to get added, so you dont import existing ones to get duplicates)
append to target table.

2. make an update query
again, join the 2 tables but INNER join
since now all records exist, you will change everyone.

put both queries in a macro,
run the macro.

[Erictsang]

Thanks but i do not understand about the inner join and outer join

[aytee111]

Use the query wizard to create an unmatched query - records from the second table which do not match the first table. Then change this into an append query. This will add all the records from the second table which do not exist in the first table.

Then have an update query which will update the first table with data in the second table.