Results 1 to 4 of 4
  1. #1
    prost is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    3

    Two values must be met for variable to return result

    Hi,

    I'd like to write a query that will for each Business Rule, return the Business Rule, Tech Function Name and Side IF there is both a LHS AND a RHS for that business rule. So for this example data:

    Business Rule Tech Function Name Side
    2 CST_CSTACRAP LHS
    2 PO_POXSCASL RHS
    2 AP_APXVDDUP RHS
    2 POS_HT_SP_B_QUK_UPD RHS
    2 AP_APXVDMVD RHS
    3 CST_CSTACRAP LHS
    3 AP_APXXXEER LHS




    The query would not return anything from Business Rule 3 since there is only a LHS, and not a LHS and a RHS. The query result would be:

    Business Rule Tech Function Name Side
    2 CST_CSTACRAP LHS
    2 PO_POXSCASL RHS
    2 AP_APXVDDUP RHS
    2 POS_HT_SP_B_QUK_UPD RHS
    2 AP_APXVDMVD RHS

    If anyone is interested this is for a Segregation of Duties IT audit. Thanks!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not proud to say I must have spent 3 hours on this, trying to coax it out of a subquery. I could get close, but never right on. Right after I shut down, I rethought my first inclination (which was to suggest a UNION query on the table) but instead, do it on two joined queries. I came up with
    qryLHS
    Code:
    SELECT DISTINCTROW tblAuditRule.[Business Rule], tblAuditRule.[Tech Function Name], tblAuditRule.Side
    FROM tblAuditRule
    WHERE (((tblAuditRule.Side)="LHS"));
    and
    qryRHS
    Code:
    SELECT DISTINCTROW tblAuditRule.[Business Rule], tblAuditRule.[Tech Function Name], tblAuditRule.Side
    FROM tblAuditRule
    WHERE (((tblAuditRule.Side)="RHS"));
    which are UNION'd by
    Code:
    SELECT qryRHS.[Business Rule], qryRHS.[Tech Function Name], qryRHS.Side FROM qryRHS INNER JOIN qryLHS ON qryRHS.[Business Rule] = qryLHS.[Business Rule]
    UNION
    SELECT qryLHS.[Business Rule], qryLHS.[Tech Function Name], qryLHS.Side FROM qryRHS INNER JOIN qryLHS ON qryRHS.[Business Rule] = qryLHS.[Business Rule]
    which looks to me like the data you want (although not ordered the same). Sure as h-e double hockey sticks hope it solves your problem. Regardless, I'm turning in!
    Business Rule Tech Function Name Side
    2 AP_APXVDDUP RHS
    2 AP_APXVDMVD RHS
    2 CST_CSTACRAP LHS
    2 PO_POXSCASL RHS
    2 POS_HT_SP_B_QUK_UPD RHS
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    prost is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    3
    3 hours? Wow, but thanks! Seems to work, and I've been able to modify it to do a bit more I'm interested in.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad it helped after all that. Even better that you could adapt it.

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

Similar Threads

  1. Replies: 5
    Last Post: 05-09-2015, 10:55 AM
  2. Return the result of a search
    By Loc in forum Programming
    Replies: 11
    Last Post: 06-12-2013, 06:23 PM
  3. Return result based on %
    By Guitarzan in forum Access
    Replies: 1
    Last Post: 08-08-2012, 09:18 AM
  4. how to return the result?
    By lamkee in forum Access
    Replies: 1
    Last Post: 08-10-2010, 10:50 AM
  5. Passing SQL result into variable
    By jonny in forum Access
    Replies: 3
    Last Post: 10-18-2009, 07:46 AM

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