Access 16 append query - Syntax error (missing operator) in query expression WHERE clause-CodePudding

I am still not too experienced with access, SQL, VBA and this is my first post. So please go easy on me! Okay lets get to it. I have been updating an access file due to irrelevant and duplicate records showing up on reports.

First I was having trouble appending records due to Key violations that I determined was due to appending to primary key fields, which I remedied by creating an autonumber ID Primary key on the destination table. I am having trouble updating the query with a WHERE clause to not add records already on the destination table. Field and table names have been changed below, but I am trying to insert into "dandy" from "bag" and "rug".

Error message reads:

Syntax error (missing operator) in query expression '"NOOB"
        WHERE [rug].[TENT] AND [rug].[CANDY] NOT IN (SELECT [TENT] AND [CANDY] FROM [dandy])'.

See SQL Code below:

INSERT INTO dandy(CANDY, FUN, DOG, TREAT, BUMPY, TENT, System )
SELECT Val([CANDY]) AS Burger, Val([FUN]) AS FUN_VAL, rug.STOVE, rug.TREAT, IIf([Bag]![Water] Is Not Null,[Bag]![Water],[rug]![BUMPY]) AS BUMPY, rug.TENT, "NOOB" AS System
FROM (rug INNER JOIN [crib] ON rug.CANDY= [crib].Dip) INNER JOIN Bag ON ([crib].FacNum = Bag.[Trip]) AND (rug.TENT = Bag.Lip)
GROUP BY Val([CANDY]), Val([FUN]), rug.STOVE, rug.TREAT, IIf([Bag]![Water] Is Not Null,[Bag]![Water],[rug]![BUMPY]), rug.TENT, "NOOB"
WHERE [rug].[TENT] AND [rug].[CANDY] NOT IN (SELECT [TENT] AND [CANDY] FROM [dandy])
HAVING (((IIf([Bag]![Water] Is Not Null,[Bag]![Water],[rug]![BUMPY])) Is Null));

CodePudding user response：

If you want to prevent duplicate pairs of Tent and Candy in table, just set a compound Index in table with those two fields. Records that would cause duplication will be rejected. The IN() criteria can be eliminated.

As for the IN() criteria, I tested your original syntax. It does not error but also does not retrieve records. Splitting into two NOT IN() expressions also failed - no records met the condition.

WHERE TENT NOT IN (SELECT TENT FROM dandy) AND CANDY NOT IN (SELECT CANDY FROM dandy)

However, actual concatenation did retrieve correct records.

SELECT * FROM datasource        
WHERE TENT & CANDY NOT IN (SELECT TENT & CANDY FROM dandy)

CodePudding user response：

At least one of your problems are the quotes, indicated by in query expression '"NOOB". I am not so sure about MS Access, however, usually in SQL you do not use double quotes. That means "NOOB" AS System should rather look like 'NOOB' AS System.