SubQuery Problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

You need to give us a bit more information about what you are trying to do...

What is the result of the query as posted?

Steve
 
Well here we go again. I'm having difficulty elminating the QNoMatch
and replacing it with code any help appreciated.
Thanks
DS


"SELECT tblPrivileges.PrivID, tblPrivileges.PrivName,
[QNoMatch].SDPrivID, " & _
"tblPrivileges.PrivType, tblPrivileges.PrivActive " & _
"FROM [QNoMatch] RIGHT JOIN tblPrivileges ON [QNoMatch].SDPrivID =
tblPrivileges.PrivID " & _
"WHERE (((tblPrivileges.PrivName) Not Like ""Title"") " & _
"AND (([QNoMatch].SDPrivID) Is Null) " & _
"AND ((tblPrivileges.PrivType)=1) " & _
"AND ((tblPrivileges.PrivActive)=-1)) " & _
"ORDER BY tblPrivileges.PrivName;"

The Contents Of QNoMatch

SELECT tblSecurityDetails.SecurityID, tblSecurityDetails.SDPrivID
FROM tblSecurityDetails
WHERE
(((tblSecurityDetails.SecurityID)=[Forms]![frmBSSecurityNames]![TxtID]));
 
SteveM said:
You need to give us a bit more information about what you are trying to do...

What is the result of the query as posted?

Steve

:

Well here we go again. I'm having difficulty elminating the QNoMatch
and replacing it with code any help appreciated.
Thanks
DS


"SELECT tblPrivileges.PrivID, tblPrivileges.PrivName,
[QNoMatch].SDPrivID, " & _
"tblPrivileges.PrivType, tblPrivileges.PrivActive " & _
"FROM [QNoMatch] RIGHT JOIN tblPrivileges ON [QNoMatch].SDPrivID =
tblPrivileges.PrivID " & _
"WHERE (((tblPrivileges.PrivName) Not Like ""Title"") " & _
"AND (([QNoMatch].SDPrivID) Is Null) " & _
"AND ((tblPrivileges.PrivType)=1) " & _
"AND ((tblPrivileges.PrivActive)=-1)) " & _
"ORDER BY tblPrivileges.PrivName;"

The Contents Of QNoMatch

SELECT tblSecurityDetails.SecurityID, tblSecurityDetails.SDPrivID
FROM tblSecurityDetails
WHERE
(((tblSecurityDetails.SecurityID)=[Forms]![frmBSSecurityNames]![TxtID]));
Click to expand...
Click to expand...
The first one returns all SDPrivID where the SecurityID mtches the TxtID
Anything that is in the query will be excluded in the second Query.
 
I know it's a mess but I think it's something like this...???

"SELECT tblPrivileges.PrivID, tblPrivileges.PrivName " & _
"FROM tblPrivileges " & _
"WHERE (((tblPrivileges.PrivName) Not Like ""Title"") " & _
"AND ((tblSecurityDetails.SDPrivID) Is Null) " & _
"AND ((tblPrivileges.PrivType)=1) " & _
"AND ((tblPrivileges.PrivActive)=-1)) " & _
UNION SELECT
SELECT tblSecurityDetails.SecurityID, tblSecurityDetails.SDPrivID
FROM tblSecurityDetails
WHERE
(((tblSecurityDetails.SecurityID)=[Forms]![frmBSSecurityNames]![TxtID]));
"ORDER BY tblPrivileges.PrivName;"
 
Something like this?

"SELECT tblPrivileges.PrivID, tblPrivileges.PrivName " & _
"FROM tblPrivileges " & _
"WHERE (((tblPrivileges.PrivName) Not Like ""Title"") " & _
"AND ((tblPrivileges.PrivType)=1) " & _
"AND ((tblPrivileges.PrivActive)=-1)) " & _
"AND NOT (tblPrivileges.PrivID) " & _
"IN(SELECT tblSecurityDetails.SDPrivID FROM tblSecurityDetails)"

Steve


DS said:
SteveM said:
I still don't know what you are trying to achieve!

Steve

:

I know it's a mess but I think it's something like this...???

"SELECT tblPrivileges.PrivID, tblPrivileges.PrivName " & _
"FROM tblPrivileges " & _
"WHERE (((tblPrivileges.PrivName) Not Like ""Title"") " & _
"AND ((tblSecurityDetails.SDPrivID) Is Null) " & _
"AND ((tblPrivileges.PrivType)=1) " & _
"AND ((tblPrivileges.PrivActive)=-1)) " & _
UNION SELECT
SELECT tblSecurityDetails.SecurityID, tblSecurityDetails.SDPrivID
FROM tblSecurityDetails
WHERE
(((tblSecurityDetails.SecurityID)=[Forms]![frmBSSecurityNames]![TxtID]));
"ORDER BY tblPrivileges.PrivName;"
Click to expand...
Click to expand...
I'm trying to pull records from tblPrivilege that are not in tblSecurity
Details.

DS
Click to expand...
 
well, even somewhat complex SQL is not my strong point, but perhaps a
subquery would work, as

"SELECT PrivID, PrivName, PrivType, PrivActive " & _
"FROM tblPrivileges WHERE PrivName Not Like 'Title' " & _
"AND PrivID <> (SELECT SDPrivID FROM " & _
"tblSecurityDetails WHERE SecurityID=" & _
Forms!frmBSSecurityName!TxtID & _
" And PrivType=1 AND PrivActive=-1 " & _
"ORDER BY PrivName;"

note that if SecurityID is a Text data type, not numeric, then you'll have
to include *single* quotes to surround the reference value, as

"tblSecurityDetails WHERE SecurityID='" & _
Forms!frmBSSecurityName!TxtID & _
"' And PrivType=1 AND PrivActive=-1 " & _

also, frmBSSecurity must be open, of course. and if the code is actually
running in frmBSSecurity's module, then change the reference, as

"tblSecurityDetails WHERE SecurityID='" & _
Me!TxtID "' And PrivType=1 AND PrivActive=-1 " & _

again, including the *single* quotes if SecurityID is Text data type, or
leaving them out if it is a Number data type (or Autonumber).

hth


DS said:
SteveM said:
I still don't know what you are trying to achieve!

Steve

:

I know it's a mess but I think it's something like this...???

"SELECT tblPrivileges.PrivID, tblPrivileges.PrivName " & _
"FROM tblPrivileges " & _
"WHERE (((tblPrivileges.PrivName) Not Like ""Title"") " & _
"AND ((tblSecurityDetails.SDPrivID) Is Null) " & _
"AND ((tblPrivileges.PrivType)=1) " & _
"AND ((tblPrivileges.PrivActive)=-1)) " & _
UNION SELECT
SELECT tblSecurityDetails.SecurityID, tblSecurityDetails.SDPrivID
FROM tblSecurityDetails
WHERE
Click to expand...
(((tblSecurityDetails.SecurityID)=[Forms]![frmBSSecurityNames]![TxtID]));
"ORDER BY tblPrivileges.PrivName;"
Click to expand...
Click to expand...
I'm trying to pull records from tblPrivilege that are not in tblSecurity
Details.

DS
Click to expand...
 
SteveM said:
I still don't know what you are trying to achieve!

Steve

:

I know it's a mess but I think it's something like this...???

"SELECT tblPrivileges.PrivID, tblPrivileges.PrivName " & _
"FROM tblPrivileges " & _
"WHERE (((tblPrivileges.PrivName) Not Like ""Title"") " & _
"AND ((tblSecurityDetails.SDPrivID) Is Null) " & _
"AND ((tblPrivileges.PrivType)=1) " & _
"AND ((tblPrivileges.PrivActive)=-1)) " & _
UNION SELECT
SELECT tblSecurityDetails.SecurityID, tblSecurityDetails.SDPrivID
FROM tblSecurityDetails
WHERE
(((tblSecurityDetails.SecurityID)=[Forms]![frmBSSecurityNames]![TxtID]));
"ORDER BY tblPrivileges.PrivName;"
Click to expand...
Click to expand...
I'm trying to pull records from tblPrivilege that are not in tblSecurity
Details.

DS
 
Hip Hip Hooray! I tested it and it seems to be working! This was a
tough nut for me to crack, so I really appreciate your time in solving
it for me! These subqueries are especially dificult to learn. Is there
anywhere that I can get more information on learning these? Once again
thank you for your help!
DS
 
Back
Top