Appending To A Table Only If Not Already in Table

[dugoneill]

I need to track orders and shipments from two different data sources, along with other items about the orders.

Basically my thinking here is a main table for the tracking, appending the new orders to it, then mapping the appropriate shipments to it.

Currently I have a query pull the orders based on order number appending to the table, the issue I have is that the same order number can be updated adding more items to the order from the original (never taking away from what is already there).

So if I just pull the order down again it would create duplicates of the items which were already on the order in the table, rather than just adding the new items.

Was thinking the best way around this was simply if Order - item combination already in table to ignore it, then just add what is left.

I am very new to Access and can't think of an efficient way to actual write this.

Any help would be appreciated.

 

[PaulO]

I think you could take two approaches?

1. Use a DELETE query, deleting all existing Detail records linked to the Header record, before running the existing APPEND query.
2. Compare the proposed Append records in the query to the existing TABLE, linking key UNIQUE records such as Order Number, Customer, Line Number, Product, Qty, with INNER JOINS and identifying any NULL records/returns from the Table. The append query will then only identify and append NEW records versus the existing records in the Table.

Either should achieve the result you want ...

 

[dugoneill]

I think you could take two approaches?

1. Use a DELETE query, deleting all existing Detail records linked to the Header record, before running the existing APPEND query.
2. Compare the proposed Append records in the query to the existing TABLE, linking key UNIQUE records such as Order Number, Customer, Line Number, Product, Qty, with INNER JOINS and identifying any NULL records/returns from the Table. The append query will then only identify and append NEW records versus the existing records in the Table.

Either should achieve the result you want ...

Thanks for the quick reply, the later is what I am looking for as I wouldn't want to constantly have to recreate all of the other items about the orders not being pulled down.