Search code examples
ms-access

Query that returns only records that do not match on a specific field

I am not an Access person, just trying to write what I think is a simple query to do the following:

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.

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.

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 http://abc.example.com the SUBSET URL may contain additional information, like http://abc.example.com/home.apsx for example.

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.

The desired output is a list of all RequestAccessEmail values that do not have a matching URL in the SUBSET table.

Query attempt

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.

Internal Site Owners
ID
Url
RequestAccessEmail
Attachments
1478
http://teamsites.example.com/sites/itfrance
[email protected]
0
1673
http://teamsites.example.com/sites/PricingSupport
[email protected]
0
1652
http://teamsites.example.com/sites/POProjectMgmt
[email protected]
0
1655
http://teamsites.example.com/sites/POSTeam
[email protected]
0
1741
http://teamsites.example.com/sites/rsa6x
[email protected]
0
1218
http://teamsites.example.com/sites/EMEAProjectSupport
[email protected]
0
65
http://projectsites.example.com/sites/folsom
[email protected]
0
1595
http://teamsites.example.com/sites/NBPCA
[email protected]
0
1664
http://teamsites.example.com/sites/prepaiddev
[email protected]
0
1634
http://teamsites.example.com/sites/patchmgmt
[email protected]
0
1961
http://os.example.com/sites/manager
[email protected]
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
Solution

  • See if something like this works for you:

    SELECT Distinct m.URL
FROM [Internal Site Owners] m, [SP Permissions Cleanup Tracking] s
WHERE s.URL Not Like "*" & m.URL & "*"

    You will need to paste the SQL in SQL View of the query design window.

    The query asks for rows where the master URL is not like the subset URL, so http://abc.example.com will match http://abc.example.com/home.apsx, but not http://home.abc.example.com. The format of a hyperlink type is usually display text#http://example.com#, so http://example.com in the master will match.

    Edit re data and comments

    query and tables

    SQL:

    SELECT DISTINCT m.RequestedAccessEmail, m.URL
FROM [Internal Site Owners] AS m, [SP Permissions Cleanup Tracking] AS s
WHERE s.URL Not Like "*" & [m].[URL] & "*"

    New version from sample data:

    SELECT [Internal Site Owners].RequestAccessEmail FROM [Internal Site Owners] 
WHERE ID NOT IN (
     SELECT [Internal Site Owners].ID
     FROM [Internal Site Owners], [SP Permissions Cleanup Tracking]
     WHERE [SP Permissions Cleanup Tracking].URL 
        Like "*" & [Internal Site Owners].[url] & "*")