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

I have two tables.  One of them is a temporary table named tblTempPurchaseOrdersImport and the other one is named tblPurchaseOrders.  I'm trying to figure out how to append records to the table tblPurchaseOrders from the table tblTempPurchaseOrdersImport ONLY when two field values from tblTempPurchaseOrdersImport do not already exist in tblPurchaseOrders.  Those two field names are PurchaseOrderNumber and PurchaseOrderLineNumber.  When they do not exist I want records from the tblTempPurchaseOrdersImport  to be ADDED to tblPurchaseOrders .  How can this be done.

I'm sorry if this is confusing.
SteveL13Asked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
You were updating the wrong table.
Test-DatabaseUpdated.accdb
0
 
PatHartmanCommented:
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.
0
 
SteveL13Author Commented:
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.

Query Example
Note:  I created two topics because at one time I was told to only present one question per topic.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
PatHartmanCommented:
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.
0
 
SteveL13Author Commented:
Same result.  I don't get it.
0
 
PatHartmanCommented:
If you want someone to look at it, please post the database.
0
 
SteveL13Author Commented:
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
0
 
SteveL13Author Commented:
Perfect.  And how confused could I possibly get?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.