Where Not Exists help! (1 Viewer)

[scifone]

Hello all-

Wondering if I could get some help.

I have two tables "tbl_GROWTH_Requests" and "tbl_GROWTH". I want to transfer/append all records from tbl_growth_requests to tbl_GROWTH, if they do not exist in tbl_Growth. The unique Identifier would be "Control" field

I am not sure what im doing wrong - Any help would be appreciated

DoCmd.RunSQL "INSERT INTO tbl_GROWTH " & _
"SELECT * " & _
"FROM tbl_GROWTH_Requests " & _
"WHERE NOT EXIST (" & _
"SELECT * FROM tbl_GROWTH " & _
" WHERE tbl_growth_requests.Control <> tbl_growth.control)"

 

[jdraw]

As a start, you are missing a "S" EXIST should be EXISTS

 

[Gasman]

Put your sql string into a string variable and debug.print it.
If you still cannot see your error, you can post the result back here, and someone will spot it.

 

[scifone]

As a start, you are missing a "S" EXIST should be EXISTS

thanks, that was just a typo in my notes

 

[ebs17]

INSERT INTO
   tbl_GROWTH
SELECT
   *
FROM
   tbl_GROWTH_Requests
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            tbl_GROWTH
         WHERE
            tbl_GROWTH_Requests.Control = tbl_GROWTH.control
      )

<> => =
NOT EXISTS is sufficient negation.

INSERT INTO
   tbl_GROWTH
SELECT
   R.*
FROM
   tbl_GROWTH_Requests AS R
      LEFT JOIN tbl_GROWTH AS G
      ON R.Control = G.Control
WHERE
   G.Control IS NULL

 

[Gasman]

thanks, that was just a typo in my notes

Always copy and paste what you are actually using.

 

[scifone]

INSERT INTO
   tbl_GROWTH
SELECT
   *
FROM
   tbl_GROWTH_Requests
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            tbl_GROWTH
         WHERE
            tbl_GROWTH_Requests.Control = tbl_GROWTH.control
      )

<> => =
NOT EXISTS is sufficient negation.

INSERT INTO
   tbl_GROWTH
SELECT
   R.*
FROM
   tbl_GROWTH_Requests AS R
      LEFT JOIN tbl_GROWTH AS G
      ON R.Control = G.Control
WHERE
   G.Control IS NULL

THANK YOU - I see where I went wrong. Didn't realize "NOT EXISTS is sufficient negation."

 

[ebs17]

If you want to understand the logic used in your original query:
A field can only have one content per record. If you now compare a table with two records against another table with 2 records, each without duplicates, then a maximum of one pair of fields can be the same, so all the others must be unequal.
A test for NOT EXISTS will then return an empty result set.