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 |