我希望以下添加查詢僅添加表中尚不存在的記錄。
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)<>""));
EG 表中資料tbl_TRUCK_train_cv如下：
id | handling | text
-------------------------
"" | "CV1/CW1" | "bla"
"" | "CV4/CW7" | "bla"
add查詢中的資料如下：
id | handling | text
-------------------------
"" | "CV3/CW12" | "bla"
"" | "CV4/CW7" | "bla"
添加查詢應該只添加，"" | "CV3/CW12" | "bla"因為該記錄不在表中tbl_TRUCK_train_cv
我不知道如何解決這個問題。在這種特殊情況下，該表是從可能具有重復值的不同查詢填充的。
uj5u.com熱心網友回復：
好的，這可以在類似于您在問題中制作的查詢的插入查詢中完成。讓我一步一步地引導您完成它：
首先，讓我們從連接表和欄位 '[handling]' 的查詢開始，它看起來像這樣：
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;
現在，您需要一個查詢來查找查詢 [qryTRUCK_CV_train_CW_2] 中不在表 [tbl_TRUCK_train_cv] 中的記錄。為此，請將 Join 陳述句更改為 LEFT JOIN。這將顯示查詢 [qryTRUCK_CV_train_CW_2] 中的所有記錄，無論表 [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;
最后，添加條件 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));
這個 WHERE 子句會將記錄限制在查詢中但不在表中的記錄！
你快到了。
接下來，讓我們將此作為分組查詢，以防查詢中的 [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;
最后，將這些結果插入表 [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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/378774.html
標籤：ms-access