Table Merge Without Dupes

Question

I have an Access 2007 database that works asynchronously with a MAS200 database. Due to various office politics and red tape, I cannot get access to the MAS200 data, even though ODBC makes it easy, and must import data dumps from MAS200 to Access. Because of this and the way reports are run, I occasionally have duplicate data on a dump.

My solution was to have a DATA table, and a DUMP table. Now I need to find the correct query to check to see if the line from DUMP exists in DATA before I import.

Both tables look like this, plus a few fields that are filled in manually after the dupe and dump logic is done:

DATA ID / DUMP ID
InvoiceDate
InvoiceNumber
CompanyID
Product
Description
SalespersonID
Quantity
Cost
Price

My assumption is that I can use the following query, but it doesn't seem to work.

INSERT INTO tblDATA(InvoiceDate, InvoiceNumber, CompanyID, Product, Description, SalespersonID, Quantity, Cost, Price)
SELECT tblDUMP(InvoiceDate, InvoiceNumber, CompanyID, Product, Description, SalespersonID, Quantity, Cost, Price)
WHERE tblDATA.InvoiceNumber != tblDUMP.InvoiceNumber AND tblDATA.Product != tblDUMP.Product AND tblDATA.Quantity != tblDUMP.Quantity

Can you fix my code or show me a better path?

---

EDIT!

My Database has changed slightly since I first posted this question. The query I am looking at correcting is now:

INSERT INTO tblSold(InvoiceDate, Invoice, CompanyID, Product, Description, Salesperson, Quantity, Cost, Price)
SELECT tblNewData.InvoiceDate, tblNewData.Invoice, tblNewData.CompanyID, tblNewData.Product, tblNewData.Description, tblNewData.Salesperson, tblNewData.Quantity, tblNewData.Cost, tblNewData.Price
FROM tblNewData FULL OUTER JOIN tblSold ON tblSold.InvoiceNumber = tblNewData.InvoiceNumber AND tblSold.Product = tblNewData.Product AND tblSold.Quantity = tblNewData.Quantity
WHERE tblSold.InvoiceNumber IS NULL AND tblSold.Product IS NULL AND tblSold.Quantity IS NULL

Answer 1

A friend wrote me a great answer that was over my head.

INSERT INTO tblDATA (InvoiceDate, InvoiceNumber, CompanyID, Product, Description, SalespersonID, Quantity, Cost, Price)
SELECT tblDUMP.InvoiceDate, tblDUMP.InvoiceNumber, tblDUMP.CompanyID, tblDUMP.Product, tblDUMP.Description, tblDUMP.SalespersonID, tblDUMP.Quantity, tblDUMP.Cost, tblDUMP.Price
FROM tblDUMP FULL OUTER JOIN tblDATA ON tblDUMP.InvoiceNumber = tblDATA.InvoiceNumber AND tblDUMP.Product = tblDATA.Product AND tblDUMP.Quantity = tblDATA.Quantity
WHERE tblDATA.InvoiceNumber IS NULL AND tblDATA.Product IS NULL AND tblDATA.Quantity IS NULL

Noting that the Syntax wouldn't be perfect in Access.

I found some great research that led me to understand his reasoning at: http://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins http://www.codinghorror.com/blog/archives/000976.html

Now I'll need help with Syntax for Access 2007.

Answer 2

Access does not support FULL OUTER JOINs. I had to use a left join and union as described here: http://bytes.com/topic/access/answers/642644-excel-vlookup-full-outer-join