CODEWITHSUNDEEP
sqlms-access
ZacAttack
ZacAttack

Reputation: 1055

How do I make an append query to where it won't append duplicate records in Access Database?

Overview
I am using a Append Query in Access Database, to move data from one table to the next.

Appraoch I have three tables, two are used for the query. The two tables are "Operations and Quotes" and "Work Orders Activated", The third which is where I am appending the data too is called "work order and opperations".

Problem
I can successfuly build an append query to add records to anonther table, but cannot get it to the point where it won't append duplicate records. Once the append query runs once I want to make it to where It will not re append those same records. I tried Using the expression builder in the for the crteiria but had no luck. I am new to SQL and it seems a little more stright forward so I was woundering what I could do to make it work with SQL instead of the expression builder???

Code

INSERT INTO WorkOrdersAndOperations ( WorkOrderID, ProcessID, TimeMin, OperationID )
SELECT WorkOrdersActivated.WorkOrderID, OperationsAndQuotes.ProcessID, OperationsAndQuotes.TimeMin, OperationsAndQuotes.OperationID
FROM WorkOrdersActivated, OperationsAndQuotes
WHERE (((WorkOrdersActivated.WorkOrderID) Not In ([WorkOrdersActivated]![WorkOrderID])));

My Where clause is what I cannot get, My expectations are that if WorkOrderID is already in WorkOrdersAndOperations table than it should not append. If you can't tell by now I am new to SQL so any help would be appreciated.

Upvotes: 0

Views: 476

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use not exists:

INSERT INTO WorkOrdersAndOperations ( WorkOrderID, ProcessID, TimeMin, OperationID )
    SELECT woa.WorkOrderID, oq.ProcessID, oq.TimeMin, oq.OperationID
FROM WorkOrdersActivated as woa,
     OperationsAndQuotes as oq
WHERE NOT EXISTS (SELECT 1
                  FROM WorkOrdersAndOperations as woo
                  WHERE woo.WorkOrderID = woa.WorkOrderID AND
                        woo.ProcessID = oq.ProcessID AND
                        woo.OperationID = oq.OperationID
                 );

Upvotes: 1

Related Questions