Results 1 to 7 of 7
  1. #1
    KelliganP's Avatar
    KelliganP is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    North Carolina, USA
    Posts
    10

    Complex filtering problem

    I have a colleague's spreadsheet that I need to model in database and I have been away from Access for quite some time. There are a series of tableted filtering conditions that I need to implement in an elegant way without hard coding them into the SQL of queries. Some are 'begins with', some 'ends with', some 'contains', etc. As well as some eliminating characteristics like 'does not equal', 'does not contain' etc.


    Click image for larger version. 

Name:	Screenshot 001.png 
Views:	20 
Size:	73.1 KB 
ID:	43851



    As the screenshot shows, the user is using SUMIFS to get his results. Does anyone have an idea how I would build something in Access to handle the filtering? I am thinking that I will need to table the conditions but I am not sure what the simplest way to do that would be.

    Thanks and Regards,
    Patrick

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in queries, use wildcards in the criteria:

    like "B*" (begins w B)
    like "*ology" (ends w 'ology')
    like "*bob*"

    or filter on the form:

    me.filter = "[field1] like 'b*'"
    me.filterON = true

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Firstly I think that the SURV* would mean Begins with SURV not Ends with.
    In Access that would equate to Like "SURV*"

    If the OR section is actually a possibility as you have described so your line 59 would be a where clause like

    WHERE ContainerName Like "SURV*" OR ContainerName IN("Bay-1", "Bay-2")

    Line 60 would be

    WHERE ContainerName Like "TARF*" AND ContainerName NOT "Tarf2"

    I can't think of an easy way to simulate this in Access on a line by line basis without a complex EVAL function, and a lot of messing around.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    KelliganP's Avatar
    KelliganP is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    North Carolina, USA
    Posts
    10
    Hi ranman256,

    Thanks for the response. I understand the response but I may not have been very clear in my explanation. I could hard code the SQL but there are so many conditions and exceptions depending on multiple conditions in a record, I was hoping for a strategy to build the conditions into a table that can be referenced by the queries. Any idea on how I could accomplish this? I suspect it could be done but I am not sure what the overall strategy would be.

    Thanks and Regards,
    Patrick

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe what you need is a type of search form where you have combos with lists of operators (<,>,=,AND, OR, etc.) and textboxes to enter the variables. Then let user define the sql string.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks to me like you are trying to apply excel concepts of managing data rather than that of a database.

    First consideration has to be what do your tables in access look like and how do they relate to each other.

    Suspect you need a number of tables and a fair amount of your calculations can probably be handled with joins and perhaps tweaks to your data as well as criteria.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Ajax. There are database concepts --tables, relationships, normalization, primary Key.....that need to be addressed. My suggestion is that you start with a clear, simple English (no jargon) description of the application that this proposed database is intended to support.

    Here is a link to a tutorial from RogersAccessLibrary that shows you how to describe the business and to build and test a data model of tables and relationships. You will experience the process by working through the tutorial ( 30-45 minutes). And what you learn can be used with any database.

    More info in the Database Planning and Design link in my signature.
    Good luck.

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

Similar Threads

  1. Replies: 5
    Last Post: 05-23-2014, 11:08 AM
  2. Complex Problem with Access 2007, Pls help.
    By adornis in forum Access
    Replies: 2
    Last Post: 12-31-2011, 05:07 PM
  3. complex design problem
    By Madmax in forum Access
    Replies: 2
    Last Post: 12-09-2011, 08:25 AM
  4. Complex Lookup Problem
    By alagrad94 in forum Programming
    Replies: 5
    Last Post: 07-27-2011, 10:24 AM
  5. Replies: 3
    Last Post: 01-14-2010, 10:42 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