How do I add records which do not already exists in a table with an add query-CodePudding

I'd like the following add query to only add records which are not already in the table.

INSERT INTO tbl_TRUCK_train_cv ( handling, [text] )
SELECT qryTRUCK_CV_train_CW_2.cv_2 AS handling, "TRUCK: " & [tblTRUCKInformation].[fldTextTXT] & Chr(10) & Chr(13) & "train: " & [tbltrainInformation].[fldTextTXT] AS [text]
FROM (qryTRUCK_CV_train_CW_2 LEFT JOIN tbltrainInformation ON qryTRUCK_CV_train_CW_2.cw = tbltrainInformation.fldID) LEFT JOIN tblTRUCKInformation ON qryTRUCK_CV_train_CW_2.cv = tblTRUCKInformation.fldID
WHERE (((qryTRUCK_CV_train_CW_2.cv)<>""));

E.G. The data in the table tbl_TRUCK_train_cv is as follows:

id | handling   | text
-------------------------
"" | "CV1/CW1"  | "bla"
"" | "CV4/CW7"  | "bla"

The data in the add query is as follows:

id | handling   | text
-------------------------
"" | "CV3/CW12" | "bla"
"" | "CV4/CW7"  | "bla"

The add query should only add "" | "CV3/CW12" | "bla" as this records is not already in table tbl_TRUCK_train_cv

I have no clue how to solve this. In this particular case the table is populated from different queries which might have duplicate values.

CodePudding user response：

OK, this can be done in an Insert query similar to the one you have crafted in your question. Let me walk you through it one step at a time:

First, lets start with a query that joins the table and the query on the field '[handling]', it looks like this:

    SELECT qryTRUCK_CV_train_CW_2.handling 
    FROM qryTRUCK_CV_train_CW_2 INNER JOIN 
    tbl_TRUCK_train_cv ON qryTRUCK_CV_train_CW_2.handling = tbl_TRUCK_train_cv.handling;

Now, you want a query that will find records in the query [qryTRUCK_CV_train_CW_2] that are NOT in the table [tbl_TRUCK_train_cv] . To do this, change the Join statement to be a LEFT JOIN. This will show all records in the query [qryTRUCK_CV_train_CW_2] regardless if there is a matching record in the table [tbl_TRUCK_train_cv].

    SELECT qryTRUCK_CV_train_CW_2.handling
    FROM qryTRUCK_CV_train_CW_2 
    LEFT JOIN tbl_TRUCK_train_cv ON qryTRUCK_CV_train_CW_2.handling = 
    tbl_TRUCK_train_cv.handling;

And finally, add the criteria WHERE [tbl_TRUCK_train_cv].handling Is Null.

    SELECT qryTRUCK_CV_train_CW_2.handling
    FROM qryTRUCK_CV_train_CW_2 LEFT JOIN tbl_TRUCK_train_cv ON 
    qryTRUCK_CV_train_CW_2.handling = tbl_TRUCK_train_cv.handling
    WHERE (((tbl_TRUCK_train_cv.handling) Is Null));

This WHERE clause will limit the records to just ones that are in the Query but not in the table!

You are almost there.

Next, lets make this a grouping query in case there are duplicates in the field [handling] in the Query.

    SELECT qryTRUCK_CV_train_CW_2.handling
    FROM qryTRUCK_CV_train_CW_2 LEFT JOIN tbl_TRUCK_train_cv ON 
    qryTRUCK_CV_train_CW_2.handling = tbl_TRUCK_train_cv.handling
    WHERE (((tbl_TRUCK_train_cv.handling) Is Null))
    GROUP BY qryTRUCK_CV_train_CW_2.handling;

And finally, to take these results and insert them into the table [tbl_TRUCK_train_cv]

    INSERT INTO tbl_TRUCK_train_cv ( handling )
    SELECT qryTRUCK_CV_train_CW_2.handling
    FROM qryTRUCK_CV_train_CW_2 LEFT JOIN tbl_TRUCK_train_cv ON 
    qryTRUCK_CV_train_CW_2.handling = tbl_TRUCK_train_cv.handling
    WHERE (((tbl_TRUCK_train_cv.handling) Is Null))
    GROUP BY qryTRUCK_CV_train_CW_2.handling;