S
SteveM
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
What is the result of the query as posted?
Steve
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
DS
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]));
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]));
D
DS
The first one returns all SDPrivID where the SecurityID mtches the TxtIDSteveM 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]));
Anything that is in the query will be excluded in the second Query.
D
DS
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;"
"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;"
S
SteveM
I still don't know what you are trying to achieve!
Steve
Steve
S
SteveM
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
"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:I'm trying to pull records from tblPrivilege that are not in tblSecuritySteveM 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;"
Details.
DS
T
tina
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
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:I'm trying to pull records from tblPrivilege that are not in tblSecuritySteveM 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;"
Details.
DS
D
DS
I'm trying to pull records from tblPrivilege that are not in tblSecuritySteveM 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;"
Details.
DS
D
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
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.