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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
PatHartmanCommented:
You were updating the wrong table.
Test-DatabaseUpdated.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Perfect.  And how confused could I possibly get?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.