title: Query that returns only records that do not match on a specific field
body: <p>I am <em>not</em> an Access person, just trying to write what I think is a simple query to do the following:</p>
  <p>I have two tables in my database, imported from two separate SharePoint lists. So I do not have control over the format of the data. I will call one table the MASTER table and one table the SUBSET table. The MASTER table contains a complete list of all possible values that could appear in the SUBSET table.</p>
  <p>Both tables have a field that contains a URL, but these fields are different data types. In the MASTER table, the URL field's data type is Text. In the SUBSET table, the URL field is of data type Hyperlink.</p>
  <p>In addition, it is possible that some values in the SUBSET URL field do not exactly match the matching value in the MASTER URL field. So, if the MASTER URL is <code>http://abc.example.com</code> the SUBSET URL may contain additional information, like <code>http://abc.example.com/home.apsx</code> for example.</p>
  <p>What I need is a query that returns all records from the MASTER table that do not have a matching URL record in the SUBSET table.</p>
  <p>The desired output is a list of all RequestAccessEmail values that do not have a matching URL in the SUBSET table.</p>
  <p><img src="https://i.stack.imgur.com/UbpxU.png" alt="Query attempt" /></p>
  <p>For each RequestAccessEmail value, include it in query results only if the associated URL value has no match in the SP Permissions Cleanup Tracking URL field.</p>
  <pre class="lang-none prettyprint-override"><code>Internal Site Owners
  ID
  Url
  RequestAccessEmail
  Attachments
  1478
  http://teamsites.example.com/sites/itfrance
  First.last1@example.com
  0
  1673
  http://teamsites.example.com/sites/PricingSupport
  First.last2@example.com
  0
  1652
  http://teamsites.example.com/sites/POProjectMgmt
  First.last3@example.com
  0
  1655
  http://teamsites.example.com/sites/POSTeam
  First.last4@example.com
  0
  1741
  http://teamsites.example.com/sites/rsa6x
  First.last5@example.com
  0
  1218
  http://teamsites.example.com/sites/EMEAProjectSupport
  First.last6@example.com
  0
  65
  http://projectsites.example.com/sites/folsom
  First.last7@example.com
  0
  1595
  http://teamsites.example.com/sites/NBPCA
  First.last8@example.com
  0
  1664
  http://teamsites.example.com/sites/prepaiddev
  First.last9@example.com
  0
  1634
  http://teamsites.example.com/sites/patchmgmt
  First.last10@example.com
  0
  1961
  http://os.example.com/sites/manager
  First.last11@example.com
  0
   
  ID
  Name
  URL
  8
  CFPB AuditsDOMAIN_e0117427.xml
  http://teamsites.example.com/sites/itfrance/home.aspx
  9
  St. Pete Legal DepartmentDOMAIN_e0117427.xml
  http://teamsites.example.com/sites/PricingSupport
  10
  Retail Contracts (7)DOMAIN_e0117427.xml
  http://teamsites.example.com/sites/POPProjectMgmt
  11
  China HR SharesDOMAIN_lc23494.xml
  http://teamsites.example.com/sites/ChinaHRShares
  12
  Client LoyaltyDOMAIN_e1033221.xml
  http://teamsites.example.com/sites/clientloyalty
  13
  eZoom! Human Resources R1.1DOMAIN_e1007952.xml
  http://teamsites.example.com/sites/ezoom-hr
  14
  IntercompanyDOMAIN_e0119924.xml
  http://teamsites.example.com/sites/intercoproject
  15
  NDMS Mentoring ProgramDOMAIN_e1033265.xml
  http://teamsites.example.com/sites/NDMSMentoring
  16
  Human ResourcesDOMAIN_e0015957.xml
  http://teamsites.example.com/sites/HR
  17
  International Human ResourcesDOMAIN_e1019184.xml
  http://teamsites.example.com/sites/GlobalHR
  18
  Site Title FSG Resource Central DOMAIN_e0102084.xml
  http://teamsites.example.com/sites/ResourceCentral/SitePages/Home.aspx
  19
  TAGDOMAIN_e1022064.xml
  http://teamsites.example.com/sites/TAG/SitePages/Home.aspx
  20
  WP Employee_Community EngagementDOMAIN_e1034583.xml
  http://teamsites.example.com/sites/WPEmpEngmt
  21
  Legal (6) - Root Site (reviewed all subsites except CID- DOJ- ACI 2011)DOMAIN_e0117427.xml
  http://legal.example.com
  22
  Consulting Services Client PortalDOMAIN_e1035127.xml
  http://clientportal.example.com/sites/consulting/default.aspx
  23
  YMCA Fitness ChallengeDOMAIN_e1035127.xml
  http://projectsites.example.com/sites/Morgano/default.aspx
  24
  OSNETDOMAIN_e0000185.xml
  http://os.example.com/Pages/default.aspx?sdupgwelredir=1
  25
  USVS_ Government SolutionsDOMAIN_e0113967.xml
  http://infoport.example.com
  26
  Global ServicesDOMAIN_e0113967.xml
  http://teamsites.example.com/sites/GlobalSvcs/default.aspx
  27
  Check Training _ Quality ServicesDOMAIN_e0113967.xml
  http://teamsites.example.com/sites/QATrain
  29
  AutoSuites ArchiveDOMAIN_e0067145.xml
  http://sp-archives.example.com/sites/autosuite/SitePages/Home.aspx
  30
  VCI Client SiteDOMAIN_e0067145.xml
  http://clientportal.example.com/sites/VCI/SitePages/Home.aspx
  31
  VCI Internal SiteDOMAIN_e0067145.xml
  http://teamsites.example.com/sites/VCI-IUO/SitePages/Home.aspx
  </code></pre>