How add records to a table from a temporary table if two fields do not have an exact match

You should have put your two questions together.  If your BE is Jet or ACE, there is a trick query that will do both at once.  Instead of using an inner join as I suggested in the other thread, use a right join.  This will not work with SQL Server but it does work with Jet and ACE.

Pat, I'm sure I'm doing something wrong.  Here is a snapshot of my query in design view.  When I run it, it is not updating the one record in the one test record I created where the two fields DO match.  Plus it is not adding new records where the two fields DO NOT match.


Note:  I created two topics because at one time I was told to only present one question per topic.

Switch the join to a left join.   I assumed that the production table would be the one on the left since that is the one you are updating.

Same result.  I don't get it.

If you want someone to look at it, please post the database.

Ok.  Attached.

Notes:

1) I made copies of the two tables in case you need to revert back to them after testing.

2) There are 4 records in tblPurchaseOrders before any query is run.

3) Purchase Order Number 4201458609, Purchase Order Line Number 30 should become a new record making the total record count 5 when the query is run because it is a new combination of the Purchase Order Number and the Purchase Order Line Number.

4) Purchase Order Number 4201670539, Purchase Order Line Number 10 should get revised since that combination already exists in tblPurchaseOrders.  The revision should make "GR Qty - Order Qty" change from -5 to -2

5) When done there should be 5 records instead of the original 4.  -  With one new record and one revised record.

Thanks much.  I sure hope you can figure this one out.

PS - There are several more fields but I've deleted them in an attempt to make this simple.

--Steve
Test-Database.accdb

Perfect.  And how confused could I possibly get?