Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    11

    Access Query to filter out list based on multiple row criteria from another table

    I have googled and also searched stack overflow but I am not able to find a solution for this.



    I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one coloumn ID and another criteria.

    I want to create a query to filter websites list which does not have values or characters from table b.
    I have these values in table B that I want to be filtered out or not shown in my URL Select Query

    .org
    .gov
    .du
    .pk
    .dk

    I would keep on adding more criterias into this so criteria table so adding new criteria into table B should not disturb our filtering.

    I thought this is easy but really I don't seem to find a solution to this. Any help is highly appreciated. Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query

    SELECT tableA.WEB_ADDRESS
    FROM tableA
    WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));

    Best regards,
    Zeeshan

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    What is in TableB ? Field names ?
    Last edited by burrina; 05-10-2014 at 04:24 PM. Reason: Explanation

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    This seems to work

    Code:
    SELECT aqryModified.Web_Address
    FROM (SELECT TableA.Web_Address, Mid([TableA].[Web_Address],InStrRev([TableA].[Web_Address],".")) AS aDomainDot
    FROM TableA) AS aqryModified LEFT JOIN TableB ON aqryModified.aDomainDot = TableB.DomainDot
    WHERE (((TableB.DomainDot) Is Null));
    I am assuming TableB has a field name of "DomainDot" where fields like .com, .gov, etc reside

  4. #4
    Join Date
    May 2011
    Posts
    11

    Access filter query

    Quote Originally Posted by burrina View Post
    What is in TableB ? Field names ?
    Hi Table B has only one field filter. So I want to keep on adding all the filters in this table and they should filter my table A. the solution provided by ITS ME is giving error.

  5. #5
    Join Date
    May 2011
    Posts
    11
    Hi ItsMe ... thanks for giving a solution but when i run it in access it gives me this error message of Invalid Procedure Call. Please help because it has been two days and I don't find a solution from anywhere. Thanks !

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I created a new query and pasted the code from post #3 into it. It worked, so long as the correct table names and field names are available.
    Attached Files Attached Files

  7. #7
    Join Date
    May 2011
    Posts
    11
    Quote Originally Posted by ItsMe View Post
    I created a new query and pasted the code from post #3 into it. It worked, so long as the correct table names and field names are available.
    Hi ItsMe, I saw your attachment and it is awesome ! You must be very experienced. Thanks for this. Just for learning will you please explain below sql query that you have used. There are certain unfamiliar terms in it like aqryModified, Mid(TableA), InStrRev([TableA].[Web_Address ... I would love to know this for learning sake and may be useful for other people also. So full marks from my side for you. Thank you very much.
    SELECT aqryModified.Web_Address
    FROM (SELECT TableA.Web_Address, Mid([TableA].[Web_Address],InStrRev([TableA].[Web_Address],".")) AS aDomainDot
    FROM TableA) AS aqryModified LEFT JOIN TableB ON aqryModified.aDomainDot = TableB.DomainDot
    WHERE (((TableB.DomainDot) Is Null));

  8. #8
    Join Date
    May 2011
    Posts
    11
    Hi ItsMe, I have tested it but as soon as I add new criterias in tableB then it gives an error "procedure call failed". Please help because I want to keep on adding criteria in table B and it should work . Since you are the inventer of that query then I think only you can help me in this.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by zeeshanaslamdurrani View Post
    Hi ItsMe, I have tested it but as soon as I add new criterias in tableB then it gives an error "procedure call failed". Please help because I want to keep on adding criteria in table B and it should work . Since you are the inventer of that query then I think only you can help me in this.
    Can you post the SQL you are trying to use where you add criteria?

    As for the example, I use a three different techniques.

    The first is to create and alias query. A while back I posed the question to June7 if she thought it was possible and she came up with SQL that does the trick. This is what aqryModified is all about. Kinda like an alias for a field but for an entire query statement.

    The second technique is to create a column that will compare directly and literally with TableB.DomainDot. I used two functions as an aggregate, Mid() and InStrRev(). The InStrRev() function retrieves the position of the last Dot within the text and passes it to the Mid() function. The result is .com or .gov, etc. The result is used in an alias.

    The third technique is to use the LEFT join and the Is Null criteria to find unmatched records on the join. This technique can be illustrated using the Wizard to build an Unmatched Query.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Edit...........
    Last edited by ItsMe; 05-13-2014 at 08:23 AM. Reason: Posted in wrong thread.

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

Similar Threads

  1. Replies: 3
    Last Post: 04-08-2014, 01:02 PM
  2. Sharepoint List Query Filter based upon User Login
    By Steven.Allman in forum SharePoint
    Replies: 5
    Last Post: 03-22-2012, 11:30 AM
  3. Replies: 0
    Last Post: 04-08-2010, 12:22 PM
  4. Replies: 1
    Last Post: 02-03-2010, 08:17 AM
  5. Replies: 1
    Last Post: 03-01-2009, 09:53 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