SQL Select Query

[reggiete]

Hello All,

I am trying to Join the 2 Select Queries below and not sure on how to. What i would like the results to show is a Fail for any user who has unapproved access.

The first Select statement checks the tbl named AAL_Matrix against the Tableau table and provides a "Fail" for anything with a null.

Second select statement, does the same check for the tbl named TblApprovedExceptions.

I would like to combine the results for both queries and only keep results where the user access was not found in both queries.

SELECT Tableau.*, "Fail" AS AccessVerification, Tableau.Access AS CurrentAccessRights
FROM Tableau LEFT JOIN AAL_Matrix ON (Tableau.Access=AAL_Matrix.[PrimaryRole]) AND (Tableau.AAL=AAL_Matrix.AAL)
WHERE (((AAL_Matrix.AAL) Is Null) AND ((AAL_Matrix.PrimaryRole) Is Null));

SELECT Tableau.*, "Fail" AS AccessVerification, Tableau.Access AS CurrentAccessRights
FROM Tableau LEFT JOIN TblApprovedExceptions ON (Tableau.Access = TblApprovedExceptions.Role) AND (Tableau.EID = TblApprovedExceptions.[EID])
WHERE (((TblApprovedExceptions.EID) Is Null) AND ((TblApprovedExceptions.Role) Is Null));

 

[Minty]

This isn't the most verbose way of doing it but will hopefully show you a suitable technique. Add a two expression fields to both queries called A and B . In the first query A: = 1 , B: =0 in the second query A: =0 and B: =1.

In the sql design view of an new query do the following

SELECT * FROM YourQuery1
UNION ALL
SELECT * FROM YourQuery2

And look at the results. You can then query the Union Query and filter the results on A=1 and B=1.

 

[plog]

The way to combine 2 queries with the same SELECT and FROM clauses is by combining their logic. Let's simplify your 2 queries by removing all the specific tables/fields. That would give us this:


SELECT S1
FROM F1
LEFT JOIN LJ1
WHERE W1


SELECT S2
FROM F2
LEFT JOIN LJ2
WHERE W2

S1 and S2 are equivalent in your code, as is F1 and F2. So your final query would start off like so:

SELECT S1
FROM F1

Now, the Left joins are different, but the way LEFT JOINS work we can just include them both. That gives us:

SELECT S1
FROM F1
LEFT JOIN LJ1
LEFT JOIN LJ2

Now, since you want either of the WHERE conditions to let data through you would use an OR like so:

SELECT S1
FROM F1
LEFT JOIN LJ1
LEFT JOIN LJ2
WHERE (W1) OR (W2)

That's how you would combine your queries into 1. The real key is using commas around the entire WHERE clauses from each query you currently have when you build this new query. Further, this could even be achieved just in Design Builder of the query. You would just need to add all the W! criteria on the same criteria line and put all the W2 criteria on another criteria line--that's the equivalent of writing 'OR' in SQL.

 

[plog]

Just reread your post and I'm confused:

I would like to combine the results for both queries and only keep results where the user access was not found in both queries.

You initially say you want to combine the results, but then you say you only what the results where its not found in both queries. Those aren't the same thing.

My initial post gave you a combination of your queries. If in fact you wanted to use the criteria of those queries you posted together, then you should use an AND instead of an OR in my original code:

SELECT S1
FROM F1
LEFT JOIN LJ1
LEFT JOIN LJ2
WHERE (W1) AND (W2)

Further, you can logically simplify your initial WHERE clauses to this:

W1 = WHERE (AAL_Matrix.AAL Is Null);
W2 = WHERE (TblApprovedExceptions.EID Is Null)

You don't need to test both fields for Null values. Your LEFT JOIN will do that for you. You only need to test 1 field for NULL to achieve what you want.

 

[reggiete]

SELECT Tableau, "Fail" AS AccessVerification, Tableau.Access AS CurrentAccessRights
FROM Tableau
LEFT JOIN AAL_Matrix ON (Tableau.[AAL] = AAL_Matrix.[AAL]) AND (Tableau.[Access] = AAL_Matrix.[PrimaryRole])
LEFT JOIN TblApprovedExceptions ON (Tableau.[EID] = TblApprovedExceptions.[EID]) AND (Tableau.[Access] = TblApprovedExceptions.[Role])
WHERE (((AAL_Matrix.AAL) Is Null) AND ((TblApprovedExceptions.Role) Is Null));


This is what i have but receive the syntax error

 

[plog]

Do you have a field in any of those tables called Tableau? In your initial queries you were bringing in the asterisk.

 

[plog]

Also, I might be wrong on the syntax for the LEFT JOINs and FROM. I think it might have to be this:

FROM (Tableau
  LEFT JOIN AAL_Matrix
  ON (Tableau.[AAL] = AAL_Matrix.[AAL]) AND (Tableau.[Access] = AAL_Matrix.[PrimaryRole])
  )
LEFT JOIN TblApprovedExceptions
ON (Tableau.[EID] = TblApprovedExceptions.[EID]) AND (Tableau.[Access] = TblApprovedExceptions.[Role])

 

[reggiete]

Ah i finally got it to work thanks PLOG!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!