title: SQL Query (Display All 'x' Where 'x' Is Not In Table '2' for field 'y' and has 'z' flag)
body: <p>I need to return all 'contacts' that do not appear in the 'delegate' table for 'event name' but do have flags in the 'contacts' table that can selected by the user for the search.</p>
  
  <p>I know the query can be broken in to 2 parts.</p>
  
  <ol>
  <li>Are they already attending this event (Does their email appear in 'delegates' table with delegates.event field matching 'event' on the user form)</li>
  </ol>
  
  <pre><code>WHERE (
    d.Event &lt;&gt; [Forms]![usf_FindCampaignContacts]![FCC_EventName]
  </code></pre>
  
  <ol start="2">
  <li>Do they match the criteria (Have they got the HR flag in 'contacts' table)</li>
  </ol>
  
  <pre><code>  AND (c.[HR-DEL]   = [Forms]![usf_FindCampaignContacts]![FCC_HRD]   OR IsNull([Forms]![usf_FindCampaignContacts]![FCC_HRD]));
  </code></pre>
  
  <p>Based on the 2 things that the query is required to do I have written the following code...</p>
  
  <pre><code>SELECT 
  c.[First Name], c.[Last Name], c.Email, d.Event, c.Suppress, c.[HR-DEL]
  
  FROM tbl_Contacts AS c LEFT JOIN tbl_Delegates AS d ON c.Email = d.Email
  
  WHERE (
    d.Event &lt;&gt; [Forms]![usf_FindCampaignContacts]![FCC_EventName]
    And 
    c.Suppress = False
  ) 
    AND (c.[HR-DEL]   = [Forms]![usf_FindCampaignContacts]![FCC_HRD]   OR IsNull([Forms]![usf_FindCampaignContacts]![FCC_HRD]));
  </code></pre>
  
  <p>[FCC_HRD] refers to the user selected input on the form, I tried to use a &lt;> to remove matching records but I feel this is where the compile error is so I changed these to and/or statements and this part now returns results with the matching flags (Success) </p>
  
  <p>Other issue with attempting to do it this way is even if it worked it would remove anyone who was listed in the delegates/sponsor table. Which is why I added the &lt;> statement for the Event as it only needs to remove them off the list for the named event. Again this works perfectly well (Success)</p>
  
  <p>Final issue is the results are clearly being pulled from the 'delegates' table not the 'contacts' table as both parts above work but only display the results that match criteria in delegates table not from contacts.</p>
  
  <p><a href="https://imgur.com/nHRmA0L" rel="nofollow noreferrer">Here is the query/table relationships</a></p>
  
  <p><a href="https://imgur.com/ttXJTc4" rel="nofollow noreferrer">Here is the user form</a> (This is not the final design)</p>
  
  <p>Below are the 3 tables that are used in the query (2 direct, 1 linked)
  Contacts (c)</p>
  
  <pre><code>+----+------------+---------------+-------------------------+--------+----------+
  | ID | First Name |   Last Name   |          Email          | HR-DEL | Suppress |
  +----+------------+---------------+-------------------------+--------+----------+
  |  1 | A          | Platt         | a.platt@fake.com        | TRUE   | TRUE     |
  |  2 | D          | Farr          | d.farr@fake.com         | TRUE   | FALSE    |
  |  3 | Y          | Helle         | y.helle@fake.com        | TRUE   | FALSE    |
  |  4 | S          | Oliphant      | soliphant@fake.com      | TRUE   | FALSE    |
  |  5 | J          | Bedell-Pearce | jbedell-pearce@fake.com | TRUE   | FALSE    |
  |  6 | J          | Walker        | j.walker@fake.com       | FALSE  | FALSE    |
  |  7 | S          | Rug           | s.rug@fake.com          | FALSE  | FALSE    |
  |  8 | D          | Brown         | d.brown@fake.com        | FALSE  | FALSE    |
  |  9 | R          | Cooper        | r.cooper@fake.com       | TRUE   | FALSE    |
  | 10 | M          | Morrall       | m.morrall@fake.com      | TRUE   | FALSE    |
  +----+------------+---------------+-------------------------+--------+----------+
  </code></pre>
  
  <p>Delegates (d)</p>
  
  <pre><code>+----+-------------------------+-------+
  | ID |          Email          | Event |
  +----+-------------------------+-------+
  |  1 | a.platt@fake.com        |     2 |
  |  2 | d.farr@fake.com         |     1 |
  |  3 | y.helle@fake.com        |     4 |
  |  4 | soliphant@fake.com      |     3 |
  |  6 | jbedell-pearce@fake.com |     2 |
  +----+-------------------------+-------+
  </code></pre>
  
  <p>Events (not direct but used to check event name drop-down on user form vs event number in delegates)</p>
  
  <pre><code>+----+------------+
  | ID | Event Name |
  +----+------------+
  |  1 | Test 1     |
  |  2 | Test 2     |
  |  3 | Test 3     |
  |  4 | Test 4     |
  +----+------------+
  </code></pre>
  
  <p>Based on form selection and this sample data I need to return the following:</p>
  
  <p>All contacts who are flagged 'HR' TRUE, not suppressed or going to event named 'test 2' (Should be 5 - I always return the names of 'delegates' not going to the event only = 3)</p>
  
  <p>Final results should be:</p>
  
  <pre><code>+----+------------+-----------+--------------------+--------+----------+
  | ID | First Name | Last Name |       Email        | HR-DEL | Suppress |
  +----+------------+-----------+--------------------+--------+----------+
  |  2 | D          | Farr      | d.farr@fake.com    | TRUE   | FALSE    |
  |  3 | Y          | Helle     | y.helle@fake.com   | TRUE   | FALSE    |
  |  4 | S          | Oliphant  | soliphant@fake.com | TRUE   | FALSE    |
  |  9 | R          | Cooper    | r.cooper@fake.com  | TRUE   | FALSE    |
  | 10 | M          | Morrall   | m.morrall@fake.com | TRUE   | FALSE    |
  +----+------------+-----------+--------------------+--------+----------+
  </code></pre>
  
  <p>At the moment it appears to be pulling results from the wrong table (d not c). I attempted to change to OUTER join type but that returned with a FROM syntax error.</p>