Table that can be auto updated with new information

[heinensk23]

I have a current table which is a list of items with costs.
Daily, I want to check the base table with a new table of costs.


The new table may have new items, but also may have some items that match the original table but with updated costs. Although the new table may not contain all the items that are on the original table.

I want to be able to do a match of the new table to the original table.
If there is a new item from the new table it would get added to the original table.
If there is a matching item already on the list, I would want the original table to now show the updated cost for that existing item.

I know how I would do this manually in excel.
Is there a way to do this automatically in access?

[ranman256]

if your table is keyed to prevent duplicates, then just run 2 queries:
1. and append query (to add new items. existing items will be ignored due to keys)
2. run update query, (to update all with new costs)
done.

[CJ_London]

you could try what is called an upsert query which will add new records and update existing ones in one hit - syntax is along these lines

UPDATE (S LEFT JOIN D ON D.ID=S.ID) SET D.F1 = S.F1, D.F2 = S.F2, D.F3 = S.F3 etc

S=source table (your 'new' table)
D=destination table (your 'original' table)
ID - a field which uniquely matches between the two tables (in your case might be an item code or name)
F1, F2, F3 - field names (can be different in each table), include all fields you want to get appended - so if item code is your ID, include it here as well