Criteria using joined tables

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

Guest

I am having trouble getting the records I need when I join two tables (SAB
Caseload Tracking and Actions Taken). I joined the two tables because
information that I want to exclude is in a column which uses a table as the
"lookup wizard". I do not want the query to report records where the column
(Final Action Taken) from one table (Actions Taken) says "investigation*";
however the query reports all records based on the other criteria and does
not exclude that info. Below is my SQL statement, I hope someone understands
this and can help me. Thanks, jlw

SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case Name],
[Actions Taken].[Final Action Taken], [SAB Caseload Tracking].[ASSIGNMENT
TYPES], [SAB Caseload Tracking].[CF Date], [SAB Caseload Tracking].[PAL
Date], [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken] ON [SAB Caseload
Tracking].[Final Action Taken] = [Actions Taken].[Final Action Taken]
WHERE ((([Actions Taken].[Final Action Taken]) Not Like "Investigation*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES]) Not Like "Advisory") AND
(([SAB Caseload Tracking].[CF Date]) Is Not Null) AND (([SAB Caseload
Tracking].[PAL Date]) Is Null) AND (([SAB Caseload Tracking].[CM/DC Date]) Is
Null));
 
Do you want to exclude all records that are not like "investigation*"? Your
code says all the conditions have to be met. If you want to eliminate any
records with "Investigation*" you need to make a change to the parentheses.
I think the following may be what you are after.


SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case Name],
[Actions Taken].[Final Action Taken]
, [SAB Caseload Tracking].[ASSIGNMENT TYPES], [SAB Caseload Tracking].[CF
Date]
, [SAB Caseload Tracking].[PAL Date]
, [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken]
ON [SAB Caseload Tracking].[Final Action Taken] = [Actions Taken].[Final
Action Taken]
WHERE [Actions Taken].[Final Action Taken] Not Like "Investigation*"
AND ([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory"
AND [SAB Caseload Tracking].[CF Date] Is Not Null
AND [SAB Caseload Tracking].[PAL Date] Is Null
AND [SAB Caseload Tracking].[CM/DC Date] Is Null)
 
I want all records where the CF date is Nut Null; PAL Date is Null; and CM/DC
Date is Null excluding records where the Final Action is Not Like
"Investigation*", Not Like "Corres*" (and so on as in the SQL) and also
excluding records where the Assignment Type is Not Like "Advisory*", Not Like
"Correspondence*", and so on. I did not realize my entire SQL did not post
last time, but I re-copied it below (I left off the SELECT and FROM
statements). I think it is still a problem with the parentheses, but I'm not
having any luck when I try different things. Any help is greatly
appreciated!!!

WHERE ([Actions Taken].[Final Action Taken] Not Like "Investigation*" Or
[Actions Taken].[Final Action Taken] Not Like "Additional*" Or [Actions
Taken].[Final Action Taken] Not Like "Corres*" Or [Actions Taken].[Final
Action Taken] Not Like "Feedback*" Or [Actions Taken].[Final Action Taken]
Not Like "NacPac*" Or [Actions Taken].[Final Action Taken] Not Like "Re-PAL*"
Or [Actions Taken].[Final Action Taken] Not Like "Resend*" Or [Actions
Taken].[Final Action Taken] Not Like "Return*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Correspondence*" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "DM Scheduling" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Other*" OR [SAB Caseload
Tracking].[ASSIGNMENT TYPES] Not Like "PID*" OR [SAB Caseload
Tracking].[ASSIGNMENT TYPES] Not Like "Potential*") AND ([SAB Caseload
Tracking].[CF Date] Is Not Null AND [SAB Caseload Tracking].[PAL Date] Is
Null AND [SAB Caseload Tracking].[CM/DC Date] Is Null))

John Spencer said:
Do you want to exclude all records that are not like "investigation*"? Your
code says all the conditions have to be met. If you want to eliminate any
records with "Investigation*" you need to make a change to the parentheses.
I think the following may be what you are after.


SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case Name],
[Actions Taken].[Final Action Taken]
, [SAB Caseload Tracking].[ASSIGNMENT TYPES], [SAB Caseload Tracking].[CF
Date]
, [SAB Caseload Tracking].[PAL Date]
, [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken]
ON [SAB Caseload Tracking].[Final Action Taken] = [Actions Taken].[Final
Action Taken]
WHERE [Actions Taken].[Final Action Taken] Not Like "Investigation*"
AND ([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory"
AND [SAB Caseload Tracking].[CF Date] Is Not Null
AND [SAB Caseload Tracking].[PAL Date] Is Null
AND [SAB Caseload Tracking].[CM/DC Date] Is Null)


jlw said:
I am having trouble getting the records I need when I join two tables (SAB
Caseload Tracking and Actions Taken). I joined the two tables because
information that I want to exclude is in a column which uses a table as
the
"lookup wizard". I do not want the query to report records where the
column
(Final Action Taken) from one table (Actions Taken) says "investigation*";
however the query reports all records based on the other criteria and does
not exclude that info. Below is my SQL statement, I hope someone
understands
this and can help me. Thanks, jlw

SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case Name],
[Actions Taken].[Final Action Taken], [SAB Caseload Tracking].[ASSIGNMENT
TYPES], [SAB Caseload Tracking].[CF Date], [SAB Caseload Tracking].[PAL
Date], [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken] ON [SAB Caseload
Tracking].[Final Action Taken] = [Actions Taken].[Final Action Taken]
WHERE ((([Actions Taken].[Final Action Taken]) Not Like "Investigation*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES]) Not Like "Advisory") AND
(([SAB Caseload Tracking].[CF Date]) Is Not Null) AND (([SAB Caseload
Tracking].[PAL Date]) Is Null) AND (([SAB Caseload Tracking].[CM/DC Date])
Is
Null));
Click to expand...
Click to expand...
 
Looks as if you need to change the ORs to ANDs.

Think about it.

If the value in a field is "Advisory" and your criteria is
Field Not Like "Advisory" OR Field Not Like "XXY" then the test is TRUE
since the field is NOT like "XXY".

You could restate the test as Not(Field Like "Advisory*" Or Field Like
"Correspondence*")

Boolean logic - don't you love it.

WHERE [Actions Taken].[Final Action Taken] Not Like "Investigation*"
AND [Actions Taken].[Final Action Taken] Not Like "Additional*"
AND [Actions Taken].[Final Action Taken] Not Like "Corres*"
AND [Actions Taken].[Final Action Taken] Not Like "Feedback*"
AND [Actions Taken].[Final Action Taken] Not Like "NacPac*"
AND [Actions Taken].[Final Action Taken] Not Like "Re-PAL*"
AND [Actions Taken].[Final Action Taken] Not Like "Resend*"
AND [Actions Taken].[Final Action Taken] Not Like "Return*"

AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Correspondence*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "DM Scheduling"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Other*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "PID*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Potential*"

AND ([SAB Caseload Tracking].[CF Date] Is Not Null
AND [SAB Caseload Tracking].[PAL Date] Is Null
AND [SAB Caseload Tracking].[CM/DC Date] Is Null

jlw said:
I want all records where the CF date is Nut Null; PAL Date is Null; and
CM/DC
Date is Null excluding records where the Final Action is Not Like
"Investigation*", Not Like "Corres*" (and so on as in the SQL) and also
excluding records where the Assignment Type is Not Like "Advisory*", Not
Like
"Correspondence*", and so on. I did not realize my entire SQL did not post
last time, but I re-copied it below (I left off the SELECT and FROM
statements). I think it is still a problem with the parentheses, but I'm
not
having any luck when I try different things. Any help is greatly
appreciated!!!

WHERE ([Actions Taken].[Final Action Taken] Not Like "Investigation*" Or
[Actions Taken].[Final Action Taken] Not Like "Additional*" Or [Actions
Taken].[Final Action Taken] Not Like "Corres*" Or [Actions Taken].[Final
Action Taken] Not Like "Feedback*" Or [Actions Taken].[Final Action Taken]
Not Like "NacPac*" Or [Actions Taken].[Final Action Taken] Not Like
"Re-PAL*"
Or [Actions Taken].[Final Action Taken] Not Like "Resend*" Or [Actions
Taken].[Final Action Taken] Not Like "Return*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory" OR
[SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Correspondence*" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "DM Scheduling" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Other*" OR [SAB Caseload
Tracking].[ASSIGNMENT TYPES] Not Like "PID*" OR [SAB Caseload
Tracking].[ASSIGNMENT TYPES] Not Like "Potential*") AND ([SAB Caseload
Tracking].[CF Date] Is Not Null AND [SAB Caseload Tracking].[PAL Date] Is
Null AND [SAB Caseload Tracking].[CM/DC Date] Is Null))

John Spencer said:
Do you want to exclude all records that are not like "investigation*"?
Your
code says all the conditions have to be met. If you want to eliminate
any
records with "Investigation*" you need to make a change to the
parentheses.
I think the following may be what you are after.


SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case Name],
[Actions Taken].[Final Action Taken]
, [SAB Caseload Tracking].[ASSIGNMENT TYPES], [SAB Caseload Tracking].[CF
Date]
, [SAB Caseload Tracking].[PAL Date]
, [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken]
ON [SAB Caseload Tracking].[Final Action Taken] = [Actions Taken].[Final
Action Taken]
WHERE [Actions Taken].[Final Action Taken] Not Like "Investigation*"
AND ([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory"
AND [SAB Caseload Tracking].[CF Date] Is Not Null
AND [SAB Caseload Tracking].[PAL Date] Is Null
AND [SAB Caseload Tracking].[CM/DC Date] Is Null)


jlw said:
I am having trouble getting the records I need when I join two tables
(SAB
Caseload Tracking and Actions Taken). I joined the two tables because
information that I want to exclude is in a column which uses a table as
the
"lookup wizard". I do not want the query to report records where the
column
(Final Action Taken) from one table (Actions Taken) says
"investigation*";
however the query reports all records based on the other criteria and
does
not exclude that info. Below is my SQL statement, I hope someone
understands
this and can help me. Thanks, jlw

SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case
Name],
[Actions Taken].[Final Action Taken], [SAB Caseload
Tracking].[ASSIGNMENT
TYPES], [SAB Caseload Tracking].[CF Date], [SAB Caseload Tracking].[PAL
Date], [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC
Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken] ON [SAB Caseload
Tracking].[Final Action Taken] = [Actions Taken].[Final Action Taken]
WHERE ((([Actions Taken].[Final Action Taken]) Not Like
"Investigation*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES]) Not Like "Advisory")
AND
(([SAB Caseload Tracking].[CF Date]) Is Not Null) AND (([SAB Caseload
Tracking].[PAL Date]) Is Null) AND (([SAB Caseload Tracking].[CM/DC
Date])
Is
Null));
Click to expand...
Click to expand...
Click to expand...
 
Okay that makes sense and I tried it but now I am not getting all the records
that I should. I think it is still trying to meet all conditions instead of
just excluding those ones "NOT LIKE". Any ideas????? Thanks for your help!

John Spencer said:
Looks as if you need to change the ORs to ANDs.

Think about it.

If the value in a field is "Advisory" and your criteria is
Field Not Like "Advisory" OR Field Not Like "XXY" then the test is TRUE
since the field is NOT like "XXY".

You could restate the test as Not(Field Like "Advisory*" Or Field Like
"Correspondence*")

Boolean logic - don't you love it.

WHERE [Actions Taken].[Final Action Taken] Not Like "Investigation*"
AND [Actions Taken].[Final Action Taken] Not Like "Additional*"
AND [Actions Taken].[Final Action Taken] Not Like "Corres*"
AND [Actions Taken].[Final Action Taken] Not Like "Feedback*"
AND [Actions Taken].[Final Action Taken] Not Like "NacPac*"
AND [Actions Taken].[Final Action Taken] Not Like "Re-PAL*"
AND [Actions Taken].[Final Action Taken] Not Like "Resend*"
AND [Actions Taken].[Final Action Taken] Not Like "Return*"

AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Correspondence*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "DM Scheduling"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Other*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "PID*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Potential*"

AND ([SAB Caseload Tracking].[CF Date] Is Not Null
AND [SAB Caseload Tracking].[PAL Date] Is Null
AND [SAB Caseload Tracking].[CM/DC Date] Is Null

jlw said:
I want all records where the CF date is Nut Null; PAL Date is Null; and
CM/DC
Date is Null excluding records where the Final Action is Not Like
"Investigation*", Not Like "Corres*" (and so on as in the SQL) and also
excluding records where the Assignment Type is Not Like "Advisory*", Not
Like
"Correspondence*", and so on. I did not realize my entire SQL did not post
last time, but I re-copied it below (I left off the SELECT and FROM
statements). I think it is still a problem with the parentheses, but I'm
not
having any luck when I try different things. Any help is greatly
appreciated!!!

WHERE ([Actions Taken].[Final Action Taken] Not Like "Investigation*" Or
[Actions Taken].[Final Action Taken] Not Like "Additional*" Or [Actions
Taken].[Final Action Taken] Not Like "Corres*" Or [Actions Taken].[Final
Action Taken] Not Like "Feedback*" Or [Actions Taken].[Final Action Taken]
Not Like "NacPac*" Or [Actions Taken].[Final Action Taken] Not Like
"Re-PAL*"
Or [Actions Taken].[Final Action Taken] Not Like "Resend*" Or [Actions
Taken].[Final Action Taken] Not Like "Return*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory" OR
[SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Correspondence*" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "DM Scheduling" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Other*" OR [SAB Caseload
Tracking].[ASSIGNMENT TYPES] Not Like "PID*" OR [SAB Caseload
Tracking].[ASSIGNMENT TYPES] Not Like "Potential*") AND ([SAB Caseload
Tracking].[CF Date] Is Not Null AND [SAB Caseload Tracking].[PAL Date] Is
Null AND [SAB Caseload Tracking].[CM/DC Date] Is Null))

John Spencer said:
Do you want to exclude all records that are not like "investigation*"?
Your
code says all the conditions have to be met. If you want to eliminate
any
records with "Investigation*" you need to make a change to the
parentheses.
I think the following may be what you are after.


SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case Name],
[Actions Taken].[Final Action Taken]
, [SAB Caseload Tracking].[ASSIGNMENT TYPES], [SAB Caseload Tracking].[CF
Date]
, [SAB Caseload Tracking].[PAL Date]
, [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken]
ON [SAB Caseload Tracking].[Final Action Taken] = [Actions Taken].[Final
Action Taken]
WHERE [Actions Taken].[Final Action Taken] Not Like "Investigation*"
AND ([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory"
AND [SAB Caseload Tracking].[CF Date] Is Not Null
AND [SAB Caseload Tracking].[PAL Date] Is Null
AND [SAB Caseload Tracking].[CM/DC Date] Is Null)


I am having trouble getting the records I need when I join two tables
(SAB
Caseload Tracking and Actions Taken). I joined the two tables because
information that I want to exclude is in a column which uses a table as
the
"lookup wizard". I do not want the query to report records where the
column
(Final Action Taken) from one table (Actions Taken) says
"investigation*";
however the query reports all records based on the other criteria and
does
not exclude that info. Below is my SQL statement, I hope someone
understands
this and can help me. Thanks, jlw

SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case
Name],
[Actions Taken].[Final Action Taken], [SAB Caseload
Tracking].[ASSIGNMENT
TYPES], [SAB Caseload Tracking].[CF Date], [SAB Caseload Tracking].[PAL
Date], [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC
Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken] ON [SAB Caseload
Tracking].[Final Action Taken] = [Actions Taken].[Final Action Taken]
WHERE ((([Actions Taken].[Final Action Taken]) Not Like
"Investigation*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES]) Not Like "Advisory")
AND
(([SAB Caseload Tracking].[CF Date]) Is Not Null) AND (([SAB Caseload
Tracking].[PAL Date]) Is Null) AND (([SAB Caseload Tracking].[CM/DC
Date])
Is
Null));
Click to expand...
Click to expand...
Click to expand...
 
I finally figured it out. I had to add to the criteria for Final Action to
also include if the value is null. Thanks again for your help, John!!

jlw said:
Okay that makes sense and I tried it but now I am not getting all the records
that I should. I think it is still trying to meet all conditions instead of
just excluding those ones "NOT LIKE". Any ideas????? Thanks for your help!

John Spencer said:
Looks as if you need to change the ORs to ANDs.

Think about it.

If the value in a field is "Advisory" and your criteria is
Field Not Like "Advisory" OR Field Not Like "XXY" then the test is TRUE
since the field is NOT like "XXY".

You could restate the test as Not(Field Like "Advisory*" Or Field Like
"Correspondence*")

Boolean logic - don't you love it.

WHERE [Actions Taken].[Final Action Taken] Not Like "Investigation*"
AND [Actions Taken].[Final Action Taken] Not Like "Additional*"
AND [Actions Taken].[Final Action Taken] Not Like "Corres*"
AND [Actions Taken].[Final Action Taken] Not Like "Feedback*"
AND [Actions Taken].[Final Action Taken] Not Like "NacPac*"
AND [Actions Taken].[Final Action Taken] Not Like "Re-PAL*"
AND [Actions Taken].[Final Action Taken] Not Like "Resend*"
AND [Actions Taken].[Final Action Taken] Not Like "Return*"

AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Correspondence*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "DM Scheduling"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Other*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "PID*"
AND [SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Potential*"

AND ([SAB Caseload Tracking].[CF Date] Is Not Null
AND [SAB Caseload Tracking].[PAL Date] Is Null
AND [SAB Caseload Tracking].[CM/DC Date] Is Null

jlw said:
I want all records where the CF date is Nut Null; PAL Date is Null; and
CM/DC
Date is Null excluding records where the Final Action is Not Like
"Investigation*", Not Like "Corres*" (and so on as in the SQL) and also
excluding records where the Assignment Type is Not Like "Advisory*", Not
Like
"Correspondence*", and so on. I did not realize my entire SQL did not post
last time, but I re-copied it below (I left off the SELECT and FROM
statements). I think it is still a problem with the parentheses, but I'm
not
having any luck when I try different things. Any help is greatly
appreciated!!!

WHERE ([Actions Taken].[Final Action Taken] Not Like "Investigation*" Or
[Actions Taken].[Final Action Taken] Not Like "Additional*" Or [Actions
Taken].[Final Action Taken] Not Like "Corres*" Or [Actions Taken].[Final
Action Taken] Not Like "Feedback*" Or [Actions Taken].[Final Action Taken]
Not Like "NacPac*" Or [Actions Taken].[Final Action Taken] Not Like
"Re-PAL*"
Or [Actions Taken].[Final Action Taken] Not Like "Resend*" Or [Actions
Taken].[Final Action Taken] Not Like "Return*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory" OR
[SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Correspondence*" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "DM Scheduling" OR [SAB
Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Other*" OR [SAB Caseload
Tracking].[ASSIGNMENT TYPES] Not Like "PID*" OR [SAB Caseload
Tracking].[ASSIGNMENT TYPES] Not Like "Potential*") AND ([SAB Caseload
Tracking].[CF Date] Is Not Null AND [SAB Caseload Tracking].[PAL Date] Is
Null AND [SAB Caseload Tracking].[CM/DC Date] Is Null))

:

Do you want to exclude all records that are not like "investigation*"?
Your
code says all the conditions have to be met. If you want to eliminate
any
records with "Investigation*" you need to make a change to the
parentheses.
I think the following may be what you are after.


SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case Name],
[Actions Taken].[Final Action Taken]
, [SAB Caseload Tracking].[ASSIGNMENT TYPES], [SAB Caseload Tracking].[CF
Date]
, [SAB Caseload Tracking].[PAL Date]
, [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken]
ON [SAB Caseload Tracking].[Final Action Taken] = [Actions Taken].[Final
Action Taken]
WHERE [Actions Taken].[Final Action Taken] Not Like "Investigation*"
AND ([SAB Caseload Tracking].[ASSIGNMENT TYPES] Not Like "Advisory"
AND [SAB Caseload Tracking].[CF Date] Is Not Null
AND [SAB Caseload Tracking].[PAL Date] Is Null
AND [SAB Caseload Tracking].[CM/DC Date] Is Null)


I am having trouble getting the records I need when I join two tables
(SAB
Caseload Tracking and Actions Taken). I joined the two tables because
information that I want to exclude is in a column which uses a table as
the
"lookup wizard". I do not want the query to report records where the
column
(Final Action Taken) from one table (Actions Taken) says
"investigation*";
however the query reports all records based on the other criteria and
does
not exclude that info. Below is my SQL statement, I hope someone
understands
this and can help me. Thanks, jlw

SELECT [SAB Caseload Tracking].CSN, [SAB Caseload Tracking].[Case
Name],
[Actions Taken].[Final Action Taken], [SAB Caseload
Tracking].[ASSIGNMENT
TYPES], [SAB Caseload Tracking].[CF Date], [SAB Caseload Tracking].[PAL
Date], [CF Date]+180 AS [180 Mark], [SAB Caseload Tracking].[CM/DC
Date]
FROM [SAB Caseload Tracking] LEFT JOIN [Actions Taken] ON [SAB Caseload
Tracking].[Final Action Taken] = [Actions Taken].[Final Action Taken]
WHERE ((([Actions Taken].[Final Action Taken]) Not Like
"Investigation*")
AND (([SAB Caseload Tracking].[ASSIGNMENT TYPES]) Not Like "Advisory")
AND
(([SAB Caseload Tracking].[CF Date]) Is Not Null) AND (([SAB Caseload
Tracking].[PAL Date]) Is Null) AND (([SAB Caseload Tracking].[CM/DC
Date])
Is
Null));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top