Results 1 to 6 of 6
  1. #1
    juhav is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2016
    Posts
    2

    SQL Select query

    I have the field1 which contains values:

    http://whois.domaintools.com/{website}
    http://hosts-file.net/default.asp?s={website}
    http://www.aboutus.org/{website}
    http://www.quantcast.com/{website}
    http://www.cubestat.com/{website}
    http://www.websiteoutlook.com/www.{website}
    http://www.statbrain.com/{website}
    http://www.builtwith.com/?{website}
    and field2 which contains values:

    http://whois.domaintools.com


    http://www.quantcast.com
    http://www.builtwith.com
    Code:
    How I can select all the values from field1 which also (only root url like http://whois.domaintools.com) are in field2 ?
    So I mean the query should give result:

    http://whois.domaintools.com/{website}
    http://www.quantcast.com/{website}
    http://www.builtwith.com/?{website}
    Thanks if you can help

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe use the Left() Mid() and or the InStr() function to parse out the URL and JOIN on the result.

  3. #3
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    I'm curious, why is this a requirement? Why do you need to do that in Access (and not Excel)?

  4. #4
    juhav is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2016
    Posts
    2
    Yes, it's maybe easier to do in Excel but I don't know how to do that.

  5. #5
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    There are many ways. You could use the FIND function with the { character as an argument to extract the first part of the URL in the first table and then compare the two tables using lookup formulas or conditional formatting. I think it's beyond the scope of this forum to go into details but this should help get you started.
    You could do this in Access I suppose, but I've no idea how you'd ever go about it using SQL. Perhaps use a temp field (Expression) to extract the URL and add as criteria to field2 : =field1? Idk. Or if it's two different tables you could simply make a temporary inner join between field2 and the "temporary" modified field1 and simply run the query.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by warmslime View Post
    ..., but I've no idea how you'd ever go about it using SQL...
    You would use functions like Left() Mid() and or the InStr() or maybe Len()

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-21-2015, 11:26 AM
  2. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  3. Replies: 1
    Last Post: 05-02-2014, 09:29 AM
  4. Replies: 5
    Last Post: 05-01-2013, 11:39 AM
  5. Replies: 7
    Last Post: 05-02-2012, 08:04 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums