Databases
StackOverflow2013
Postsdbo
POTable Merge Without Dupes
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTable Merge Without Dupes
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>Both tables look like this, plus a few fields that are filled in manually after the dupe and dump logic is done:</p> <pre><code>DATA ID / DUMP ID InvoiceDate InvoiceNumber CompanyID Product Description SalespersonID Quantity Cost Price </code></pre> <p>My assumption is that I can use the following query, but it doesn't seem to work.</p> <pre><code>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 </code></pre> <p>Can you fix my code or show me a better path?</p> <hr> <p>EDIT!</p> <p>My Database has changed slightly since I first posted this question. The query I am looking at correcting is now:</p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNickSentowski
    1. USNickSentowski
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload