Select Query

B

BBAL20

I'm trying to build this query that has several and or statements in the
WHERE clause. For some reason, Access does not recognize every filter and
gives me exactly what I'm trying to filter out in the WHERE clause. Is there
a way to make this query work using only one query or do I have to build
several queries on top of another to get the results I'm expecting. I'vw
pasted the Access SQL statement below for your review. Thank you in adavance
for your help and support.

SELECT [200712].CHD_ACCOUNT_NUMBER, [200712].CHD_AGENT_BANK,
[200712].Channel, [200712].CHD_EXTERNAL_STATUS, [200712].CURRENT_BALANCE,
[200712].CHD_CREDIT_SCORE, [200712].CHD_CREDIT_LINE, [200712].PropAdrState,
[200712].IMB_LOAN_NUMBER, [200712].Loan_Doc, [200712].CHD_PORTFOLIO_NO,
[200712].CHD_UD_RPT2, [200712].Loan_Purpose, [200712].CHD_UD_RPT3,
[200712].NO_DAYS_DELINQ, [200712].DATE_OF_DATA, [200712].OccupancyType,
[200712].FICO_Bands, [200712].CLTV_Bands, [200712].DQ_Status,
[200712].Business_Units, [200712].Channel2, [200712].Portfolio1,
[200712].Portfolio2, [200712].ConduitFlag, [200712].CHD_OPEN_DATE,
[200712].Vintage, [200712].Quarter, [200712].CURRENT_UPB_AMT,
[200712].UP_INT_CHARGED_AMT, [200712].UP_FINANCE_CHARGES,
[200712].SellerLoanNum, [200712].CHDLS_BILLED_PAY_DUE,
[200712].CHD_CTD_UNPAID_BPD, [200712].CHDHD_PAYMENT_HISTORY,
[200712].CHDHD_AMT_LAST_PAYMENT, [200712].CHDHD_DATE_LAST_PAYMENT,
[200712].CHD_PRINCIPAL_NAME, [200712].CHD_SOC_SECURITY_NO,
[200712].CHD_SPOUSE_NAME, [200712].CHD_STATE, [200712].CHD_ZIP_CODE,
[200712].CHD_UD_GEO_CODE, [200712].CHD_MISCELLANEOUS_FIELD_6,
[200712].CHD_UD_RPT1, [200712].CHD_MISCELLANEOUS_FIELD_12,
[200712].CHD_MISCELLANEOUS_FIELD_11, [200712].CHD_MISCELLANEOUS_FIELD_10,
[200712].CHD_CRRN_ANNL_CASH_RT, [200712].CHD_MISCELLANEOUS_FIELD_9,
[200712].CHD_MISCELLANEOUS_FIELD_5, [200712].CHD_MISCELLANEOUS_FIELD_3,
[200712].CHD_NEW_XREF_NO_1, [200712].CHD_NEW_XREF_NO_2, [200712].CHD_UPC_11,
[200712].CHD_MISC_FIELD_13_TXT, [200712].CHD_CYCLE_CODE_99,
[200712].CHD_DATE_STATUS_CHG, [200712].LTV
FROM 200712
WHERE ((([200712].CHD_PORTFOLIO_NO) Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")))
OR ((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U")) AND
(([200712].CURRENT_BALANCE)=0)) OR ((([200712].CURRENT_BALANCE) Is Not Null));
 
C

Conan Kelly

BBAL20,

Hopefully this will help you out:

When ever you are excluding (<>) multiple values in a query, you have to use
AND.

Think of it this way:
--(([200712].CHD_EXTERNAL_STATUS)<>"Z") OR
(([200712].CHD_EXTERNAL_STATUS)<>"C")
--Lets say one record has a C in this field. It would be excluded with
(([200712].CHD_EXTERNAL_STATUS)<>"C") alone, but because you are using OR
with (([200712].CHD_EXTERNAL_STATUS)<>"Z"), it is included. It is kinda
hard to explain in plain english, but I'll give it a try. A record with a C
will be excluded with <>"C", but because you are using OR <>"Z", a C is not
a Z......because a C <> Z, the C will be included with OR <>"Z"
--(([200712].CHD_EXTERNAL_STATUS)<>"Z") AND
(([200712].CHD_EXTERNAL_STATUS)<>"C") will exclude all records with C's or
Z's in this field.

Also, I'm not too familiar with using "NOT IN ("C","L","U")", but I'm
assuming that you are trying to exclude all C's, L's, & U's......so I'm
guessing that....

((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U"))

....is the same thing as...

(([200712].CHD_EXTERNAL_STATUS)<>"C") AND
(([200712].CHD_EXTERNAL_STATUS)<>"L") AND
(([200712].CHD_EXTERNAL_STATUS)<>"U")

If that is so, then correct me if I'm wrong, but couldn't...

((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U"))

....be simplified to...

((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U","Z"))

Just to clarify, you are trying to EXCLUDE:
--the 17 portfolio numbers listed
--also any records that have an external status of Z, C, L, or U
--also any records that have a NULL balancce.

If that is the case, then change all of your OR's to AND's.

Concerning the last item, you want to include zero balances? If that is the
case, I think you can eliminate (([200712].CURRENT_BALANCE)=0)). If balance
= 0, then it is not null and zero-balance records will be included with
((([200712].CURRENT_BALANCE) Is Not Null)) alone.

Please reply with more information if my assumptions are incorrect, i.e.
what you want your results to be and what they actually are.

HTH,

Conan







BBAL20 said:
I'm trying to build this query that has several and or statements in the
WHERE clause. For some reason, Access does not recognize every filter and
gives me exactly what I'm trying to filter out in the WHERE clause. Is
there
a way to make this query work using only one query or do I have to build
several queries on top of another to get the results I'm expecting. I'vw
pasted the Access SQL statement below for your review. Thank you in
adavance
for your help and support.

SELECT [200712].CHD_ACCOUNT_NUMBER, [200712].CHD_AGENT_BANK,
[200712].Channel, [200712].CHD_EXTERNAL_STATUS, [200712].CURRENT_BALANCE,
[200712].CHD_CREDIT_SCORE, [200712].CHD_CREDIT_LINE,
[200712].PropAdrState,
[200712].IMB_LOAN_NUMBER, [200712].Loan_Doc, [200712].CHD_PORTFOLIO_NO,
[200712].CHD_UD_RPT2, [200712].Loan_Purpose, [200712].CHD_UD_RPT3,
[200712].NO_DAYS_DELINQ, [200712].DATE_OF_DATA, [200712].OccupancyType,
[200712].FICO_Bands, [200712].CLTV_Bands, [200712].DQ_Status,
[200712].Business_Units, [200712].Channel2, [200712].Portfolio1,
[200712].Portfolio2, [200712].ConduitFlag, [200712].CHD_OPEN_DATE,
[200712].Vintage, [200712].Quarter, [200712].CURRENT_UPB_AMT,
[200712].UP_INT_CHARGED_AMT, [200712].UP_FINANCE_CHARGES,
[200712].SellerLoanNum, [200712].CHDLS_BILLED_PAY_DUE,
[200712].CHD_CTD_UNPAID_BPD, [200712].CHDHD_PAYMENT_HISTORY,
[200712].CHDHD_AMT_LAST_PAYMENT, [200712].CHDHD_DATE_LAST_PAYMENT,
[200712].CHD_PRINCIPAL_NAME, [200712].CHD_SOC_SECURITY_NO,
[200712].CHD_SPOUSE_NAME, [200712].CHD_STATE, [200712].CHD_ZIP_CODE,
[200712].CHD_UD_GEO_CODE, [200712].CHD_MISCELLANEOUS_FIELD_6,
[200712].CHD_UD_RPT1, [200712].CHD_MISCELLANEOUS_FIELD_12,
[200712].CHD_MISCELLANEOUS_FIELD_11, [200712].CHD_MISCELLANEOUS_FIELD_10,
[200712].CHD_CRRN_ANNL_CASH_RT, [200712].CHD_MISCELLANEOUS_FIELD_9,
[200712].CHD_MISCELLANEOUS_FIELD_5, [200712].CHD_MISCELLANEOUS_FIELD_3,
[200712].CHD_NEW_XREF_NO_1, [200712].CHD_NEW_XREF_NO_2,
[200712].CHD_UPC_11,
[200712].CHD_MISC_FIELD_13_TXT, [200712].CHD_CYCLE_CODE_99,
[200712].CHD_DATE_STATUS_CHG, [200712].LTV
FROM 200712
WHERE ((([200712].CHD_PORTFOLIO_NO) Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")))
OR ((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U")) AND
(([200712].CURRENT_BALANCE)=0)) OR ((([200712].CURRENT_BALANCE) Is Not
Null));
Click to expand...
 
M

Michael Gramelspacher

I'm trying to build this query that has several and or statements in the
WHERE clause. For some reason, Access does not recognize every filter and
gives me exactly what I'm trying to filter out in the WHERE clause. Is there
a way to make this query work using only one query or do I have to build
several queries on top of another to get the results I'm expecting. I'vw
pasted the Access SQL statement below for your review. Thank you in adavance
for your help and support.

SELECT [200712].CHD_ACCOUNT_NUMBER, [200712].CHD_AGENT_BANK,
[200712].Channel, [200712].CHD_EXTERNAL_STATUS, [200712].CURRENT_BALANCE,
[200712].CHD_CREDIT_SCORE, [200712].CHD_CREDIT_LINE, [200712].PropAdrState,
[200712].IMB_LOAN_NUMBER, [200712].Loan_Doc, [200712].CHD_PORTFOLIO_NO,
[200712].CHD_UD_RPT2, [200712].Loan_Purpose, [200712].CHD_UD_RPT3,
[200712].NO_DAYS_DELINQ, [200712].DATE_OF_DATA, [200712].OccupancyType,
[200712].FICO_Bands, [200712].CLTV_Bands, [200712].DQ_Status,
[200712].Business_Units, [200712].Channel2, [200712].Portfolio1,
[200712].Portfolio2, [200712].ConduitFlag, [200712].CHD_OPEN_DATE,
[200712].Vintage, [200712].Quarter, [200712].CURRENT_UPB_AMT,
[200712].UP_INT_CHARGED_AMT, [200712].UP_FINANCE_CHARGES,
[200712].SellerLoanNum, [200712].CHDLS_BILLED_PAY_DUE,
[200712].CHD_CTD_UNPAID_BPD, [200712].CHDHD_PAYMENT_HISTORY,
[200712].CHDHD_AMT_LAST_PAYMENT, [200712].CHDHD_DATE_LAST_PAYMENT,
[200712].CHD_PRINCIPAL_NAME, [200712].CHD_SOC_SECURITY_NO,
[200712].CHD_SPOUSE_NAME, [200712].CHD_STATE, [200712].CHD_ZIP_CODE,
[200712].CHD_UD_GEO_CODE, [200712].CHD_MISCELLANEOUS_FIELD_6,
[200712].CHD_UD_RPT1, [200712].CHD_MISCELLANEOUS_FIELD_12,
[200712].CHD_MISCELLANEOUS_FIELD_11, [200712].CHD_MISCELLANEOUS_FIELD_10,
[200712].CHD_CRRN_ANNL_CASH_RT, [200712].CHD_MISCELLANEOUS_FIELD_9,
[200712].CHD_MISCELLANEOUS_FIELD_5, [200712].CHD_MISCELLANEOUS_FIELD_3,
[200712].CHD_NEW_XREF_NO_1, [200712].CHD_NEW_XREF_NO_2, [200712].CHD_UPC_11,
[200712].CHD_MISC_FIELD_13_TXT, [200712].CHD_CYCLE_CODE_99,
[200712].CHD_DATE_STATUS_CHG, [200712].LTV
FROM 200712
WHERE ((([200712].CHD_PORTFOLIO_NO) Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")))
OR ((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U")) AND
(([200712].CURRENT_BALANCE)=0)) OR ((([200712].CURRENT_BALANCE) Is Not Null));
Click to expand...


try:

WHERE NOT([200712].CHD_PORTFOLIO_NO
IN(('00002','00003','00010','00012','00013','00014','00011','00004','00005','00006','00007','00008','00009','00020','09996','09997','09998')
OR [200712].CHD_EXTERNAL_STATUS = 'Z' OR [200712].CHD_EXTERNAL_STATUS
IN('C','L','U')) AND ( [200712].CURRENT_BALANCE = 0 OR [200712].CURRENT_BALANCE
IS NULL);
 
B

BBAL20

I think I may have overlooked some things in my last post. Let me clarify.

1st- I want to exlude everything in the CHD_PORTFOLIO_NO column: Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")
So I want to exclude any value that matches any of the values listed above.

2nd- I want to exclude everything in the CHD_EXTERNAL_STATUS column with a
status of "Z":
<>"Z"

3rd- I want to exclude the combination of "C","L","U" in the
CHD_EXTERNAL_STATUS column with a value of 0 on the CURRENT_BALANCE column. I
basically want to eliminate any record with "C","L","U" =0.

4th- Lastly, I want to eliminate any record that does not have a value in
the CURRENT_BALANCE column. Exclude any record that is blank or null.

I know I'm just getting my And/Or mixed up. Hopefully this helps and
clarifies a bit more as to what I'm trying to do. Thank you so much Conan.

Conan Kelly said:
BBAL20,

Hopefully this will help you out:

When ever you are excluding (<>) multiple values in a query, you have to use
AND.

Think of it this way:
--(([200712].CHD_EXTERNAL_STATUS)<>"Z") OR
(([200712].CHD_EXTERNAL_STATUS)<>"C")
--Lets say one record has a C in this field. It would be excluded with
(([200712].CHD_EXTERNAL_STATUS)<>"C") alone, but because you are using OR
with (([200712].CHD_EXTERNAL_STATUS)<>"Z"), it is included. It is kinda
hard to explain in plain english, but I'll give it a try. A record with a C
will be excluded with <>"C", but because you are using OR <>"Z", a C is not
a Z......because a C <> Z, the C will be included with OR <>"Z"
--(([200712].CHD_EXTERNAL_STATUS)<>"Z") AND
(([200712].CHD_EXTERNAL_STATUS)<>"C") will exclude all records with C's or
Z's in this field.

Also, I'm not too familiar with using "NOT IN ("C","L","U")", but I'm
assuming that you are trying to exclude all C's, L's, & U's......so I'm
guessing that....

((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U"))

....is the same thing as...

(([200712].CHD_EXTERNAL_STATUS)<>"C") AND
(([200712].CHD_EXTERNAL_STATUS)<>"L") AND
(([200712].CHD_EXTERNAL_STATUS)<>"U")

If that is so, then correct me if I'm wrong, but couldn't...

((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U"))

....be simplified to...

((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U","Z"))

Just to clarify, you are trying to EXCLUDE:
--the 17 portfolio numbers listed
--also any records that have an external status of Z, C, L, or U
--also any records that have a NULL balancce.

If that is the case, then change all of your OR's to AND's.

Concerning the last item, you want to include zero balances? If that is the
case, I think you can eliminate (([200712].CURRENT_BALANCE)=0)). If balance
= 0, then it is not null and zero-balance records will be included with
((([200712].CURRENT_BALANCE) Is Not Null)) alone.

Please reply with more information if my assumptions are incorrect, i.e.
what you want your results to be and what they actually are.

HTH,

Conan







BBAL20 said:
I'm trying to build this query that has several and or statements in the
WHERE clause. For some reason, Access does not recognize every filter and
gives me exactly what I'm trying to filter out in the WHERE clause. Is
there
a way to make this query work using only one query or do I have to build
several queries on top of another to get the results I'm expecting. I'vw
pasted the Access SQL statement below for your review. Thank you in
adavance
for your help and support.

SELECT [200712].CHD_ACCOUNT_NUMBER, [200712].CHD_AGENT_BANK,
[200712].Channel, [200712].CHD_EXTERNAL_STATUS, [200712].CURRENT_BALANCE,
[200712].CHD_CREDIT_SCORE, [200712].CHD_CREDIT_LINE,
[200712].PropAdrState,
[200712].IMB_LOAN_NUMBER, [200712].Loan_Doc, [200712].CHD_PORTFOLIO_NO,
[200712].CHD_UD_RPT2, [200712].Loan_Purpose, [200712].CHD_UD_RPT3,
[200712].NO_DAYS_DELINQ, [200712].DATE_OF_DATA, [200712].OccupancyType,
[200712].FICO_Bands, [200712].CLTV_Bands, [200712].DQ_Status,
[200712].Business_Units, [200712].Channel2, [200712].Portfolio1,
[200712].Portfolio2, [200712].ConduitFlag, [200712].CHD_OPEN_DATE,
[200712].Vintage, [200712].Quarter, [200712].CURRENT_UPB_AMT,
[200712].UP_INT_CHARGED_AMT, [200712].UP_FINANCE_CHARGES,
[200712].SellerLoanNum, [200712].CHDLS_BILLED_PAY_DUE,
[200712].CHD_CTD_UNPAID_BPD, [200712].CHDHD_PAYMENT_HISTORY,
[200712].CHDHD_AMT_LAST_PAYMENT, [200712].CHDHD_DATE_LAST_PAYMENT,
[200712].CHD_PRINCIPAL_NAME, [200712].CHD_SOC_SECURITY_NO,
[200712].CHD_SPOUSE_NAME, [200712].CHD_STATE, [200712].CHD_ZIP_CODE,
[200712].CHD_UD_GEO_CODE, [200712].CHD_MISCELLANEOUS_FIELD_6,
[200712].CHD_UD_RPT1, [200712].CHD_MISCELLANEOUS_FIELD_12,
[200712].CHD_MISCELLANEOUS_FIELD_11, [200712].CHD_MISCELLANEOUS_FIELD_10,
[200712].CHD_CRRN_ANNL_CASH_RT, [200712].CHD_MISCELLANEOUS_FIELD_9,
[200712].CHD_MISCELLANEOUS_FIELD_5, [200712].CHD_MISCELLANEOUS_FIELD_3,
[200712].CHD_NEW_XREF_NO_1, [200712].CHD_NEW_XREF_NO_2,
[200712].CHD_UPC_11,
[200712].CHD_MISC_FIELD_13_TXT, [200712].CHD_CYCLE_CODE_99,
[200712].CHD_DATE_STATUS_CHG, [200712].LTV
FROM 200712
WHERE ((([200712].CHD_PORTFOLIO_NO) Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")))
OR ((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U")) AND
(([200712].CURRENT_BALANCE)=0)) OR ((([200712].CURRENT_BALANCE) Is Not
Null));
Click to expand...
Click to expand...
 
C

Conan Kelly

BBAL20,

Maybe not so much the AND's/OR's, but more the parentheses. Think of this
as regular math. Put parentheses around the groups you want to do first (or
the combinations with special criteria).

I'm gonna try to put separate criteria (or groups of criteria) on separate
lines, but I'm pretty sure Outlook Express is gonna break up the lines even
more.

See if this will work for your WHERE clause:



WHERE
[200712].CHD_PORTFOLIO_NO Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")

AND

[200712].CHD_EXTERNAL_STATUS<>"Z"

AND

NOT ([200712].CHD_EXTERNAL_STATUS In ("C","L","U") AND
[200712].CURRENT_BALANCE=0)

AND

[200712].CURRENT_BALANCE Is Not Null



Pay special attention to the External Status = C, L, or U & Balance = 0
line. I first set External Status = C, L, or U. Then I set Balance = 0.
Combined the 2 with an AND. Surrounded the whole thing with parentheses.
Finally, I flipped it (changed it from including this combination to
excluding it) with NOT and put AND's on both sides.

You should be able to copy everything from "WHERE...Is Not Null" and paste
straight into the SQL view of the query in Access, replacing your original
WHERE clause. PLEASE MAKE SURE TO COPY YOUR ORIGINAL SQL SOMEWHERE SO IT
ISN'T LOST.

Make sure there is a ";" at the end. Access will ignore (and probably
remove) all of the extra white space (spaces, tabs, new lines/carrige
returns/line breaks) and add a whole bunch of unnecessary parentheses when
you flip to design/results view.

I THINK this is correct...I have not tested it, but I hope this will do what
you are asking.

HTH,

Conan











BBAL20 said:
I think I may have overlooked some things in my last post. Let me clarify.

1st- I want to exlude everything in the CHD_PORTFOLIO_NO column: Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")
So I want to exclude any value that matches any of the values listed
above.

2nd- I want to exclude everything in the CHD_EXTERNAL_STATUS column with a
status of "Z":
<>"Z"

3rd- I want to exclude the combination of "C","L","U" in the
CHD_EXTERNAL_STATUS column with a value of 0 on the CURRENT_BALANCE
column. I
basically want to eliminate any record with "C","L","U" =0.

4th- Lastly, I want to eliminate any record that does not have a value in
the CURRENT_BALANCE column. Exclude any record that is blank or null.

I know I'm just getting my And/Or mixed up. Hopefully this helps and
clarifies a bit more as to what I'm trying to do. Thank you so much Conan.

Conan Kelly said:
BBAL20,

Hopefully this will help you out:

When ever you are excluding (<>) multiple values in a query, you have to
use
AND.

Think of it this way:
--(([200712].CHD_EXTERNAL_STATUS)<>"Z") OR
(([200712].CHD_EXTERNAL_STATUS)<>"C")
--Lets say one record has a C in this field. It would be excluded with
(([200712].CHD_EXTERNAL_STATUS)<>"C") alone, but because you are using OR
with (([200712].CHD_EXTERNAL_STATUS)<>"Z"), it is included. It is kinda
hard to explain in plain english, but I'll give it a try. A record with
a C
will be excluded with <>"C", but because you are using OR <>"Z", a C is
not
a Z......because a C <> Z, the C will be included with OR <>"Z"
--(([200712].CHD_EXTERNAL_STATUS)<>"Z") AND
(([200712].CHD_EXTERNAL_STATUS)<>"C") will exclude all records with C's
or
Z's in this field.

Also, I'm not too familiar with using "NOT IN ("C","L","U")", but I'm
assuming that you are trying to exclude all C's, L's, & U's......so I'm
guessing that....

((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U"))

....is the same thing as...

(([200712].CHD_EXTERNAL_STATUS)<>"C") AND
(([200712].CHD_EXTERNAL_STATUS)<>"L") AND
(([200712].CHD_EXTERNAL_STATUS)<>"U")

If that is so, then correct me if I'm wrong, but couldn't...

((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U"))

....be simplified to...

((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U","Z"))

Just to clarify, you are trying to EXCLUDE:
--the 17 portfolio numbers listed
--also any records that have an external status of Z, C, L, or U
--also any records that have a NULL balancce.

If that is the case, then change all of your OR's to AND's.

Concerning the last item, you want to include zero balances? If that is
the
case, I think you can eliminate (([200712].CURRENT_BALANCE)=0)). If
balance
= 0, then it is not null and zero-balance records will be included with
((([200712].CURRENT_BALANCE) Is Not Null)) alone.

Please reply with more information if my assumptions are incorrect, i.e.
what you want your results to be and what they actually are.

HTH,

Conan







BBAL20 said:
I'm trying to build this query that has several and or statements in
the
WHERE clause. For some reason, Access does not recognize every filter
and
gives me exactly what I'm trying to filter out in the WHERE clause. Is
there
a way to make this query work using only one query or do I have to
build
several queries on top of another to get the results I'm expecting.
I'vw
pasted the Access SQL statement below for your review. Thank you in
adavance
for your help and support.

SELECT [200712].CHD_ACCOUNT_NUMBER, [200712].CHD_AGENT_BANK,
[200712].Channel, [200712].CHD_EXTERNAL_STATUS,
[200712].CURRENT_BALANCE,
[200712].CHD_CREDIT_SCORE, [200712].CHD_CREDIT_LINE,
[200712].PropAdrState,
[200712].IMB_LOAN_NUMBER, [200712].Loan_Doc, [200712].CHD_PORTFOLIO_NO,
[200712].CHD_UD_RPT2, [200712].Loan_Purpose, [200712].CHD_UD_RPT3,
[200712].NO_DAYS_DELINQ, [200712].DATE_OF_DATA, [200712].OccupancyType,
[200712].FICO_Bands, [200712].CLTV_Bands, [200712].DQ_Status,
[200712].Business_Units, [200712].Channel2, [200712].Portfolio1,
[200712].Portfolio2, [200712].ConduitFlag, [200712].CHD_OPEN_DATE,
[200712].Vintage, [200712].Quarter, [200712].CURRENT_UPB_AMT,
[200712].UP_INT_CHARGED_AMT, [200712].UP_FINANCE_CHARGES,
[200712].SellerLoanNum, [200712].CHDLS_BILLED_PAY_DUE,
[200712].CHD_CTD_UNPAID_BPD, [200712].CHDHD_PAYMENT_HISTORY,
[200712].CHDHD_AMT_LAST_PAYMENT, [200712].CHDHD_DATE_LAST_PAYMENT,
[200712].CHD_PRINCIPAL_NAME, [200712].CHD_SOC_SECURITY_NO,
[200712].CHD_SPOUSE_NAME, [200712].CHD_STATE, [200712].CHD_ZIP_CODE,
[200712].CHD_UD_GEO_CODE, [200712].CHD_MISCELLANEOUS_FIELD_6,
[200712].CHD_UD_RPT1, [200712].CHD_MISCELLANEOUS_FIELD_12,
[200712].CHD_MISCELLANEOUS_FIELD_11,
[200712].CHD_MISCELLANEOUS_FIELD_10,
[200712].CHD_CRRN_ANNL_CASH_RT, [200712].CHD_MISCELLANEOUS_FIELD_9,
[200712].CHD_MISCELLANEOUS_FIELD_5, [200712].CHD_MISCELLANEOUS_FIELD_3,
[200712].CHD_NEW_XREF_NO_1, [200712].CHD_NEW_XREF_NO_2,
[200712].CHD_UPC_11,
[200712].CHD_MISC_FIELD_13_TXT, [200712].CHD_CYCLE_CODE_99,
[200712].CHD_DATE_STATUS_CHG, [200712].LTV
FROM 200712
WHERE ((([200712].CHD_PORTFOLIO_NO) Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")))
OR ((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U")) AND
(([200712].CURRENT_BALANCE)=0)) OR ((([200712].CURRENT_BALANCE) Is Not
Null));
Click to expand...
Click to expand...
Click to expand...
 
B

BBAL20

Thank you Conan. I'll give it a try.

Conan Kelly said:
BBAL20,

Maybe not so much the AND's/OR's, but more the parentheses. Think of this
as regular math. Put parentheses around the groups you want to do first (or
the combinations with special criteria).

I'm gonna try to put separate criteria (or groups of criteria) on separate
lines, but I'm pretty sure Outlook Express is gonna break up the lines even
more.

See if this will work for your WHERE clause:



WHERE
[200712].CHD_PORTFOLIO_NO Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")

AND

[200712].CHD_EXTERNAL_STATUS<>"Z"

AND

NOT ([200712].CHD_EXTERNAL_STATUS In ("C","L","U") AND
[200712].CURRENT_BALANCE=0)

AND

[200712].CURRENT_BALANCE Is Not Null



Pay special attention to the External Status = C, L, or U & Balance = 0
line. I first set External Status = C, L, or U. Then I set Balance = 0.
Combined the 2 with an AND. Surrounded the whole thing with parentheses.
Finally, I flipped it (changed it from including this combination to
excluding it) with NOT and put AND's on both sides.

You should be able to copy everything from "WHERE...Is Not Null" and paste
straight into the SQL view of the query in Access, replacing your original
WHERE clause. PLEASE MAKE SURE TO COPY YOUR ORIGINAL SQL SOMEWHERE SO IT
ISN'T LOST.

Make sure there is a ";" at the end. Access will ignore (and probably
remove) all of the extra white space (spaces, tabs, new lines/carrige
returns/line breaks) and add a whole bunch of unnecessary parentheses when
you flip to design/results view.

I THINK this is correct...I have not tested it, but I hope this will do what
you are asking.

HTH,

Conan











BBAL20 said:
I think I may have overlooked some things in my last post. Let me clarify.

1st- I want to exlude everything in the CHD_PORTFOLIO_NO column: Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")
So I want to exclude any value that matches any of the values listed
above.

2nd- I want to exclude everything in the CHD_EXTERNAL_STATUS column with a
status of "Z":
<>"Z"

3rd- I want to exclude the combination of "C","L","U" in the
CHD_EXTERNAL_STATUS column with a value of 0 on the CURRENT_BALANCE
column. I
basically want to eliminate any record with "C","L","U" =0.

4th- Lastly, I want to eliminate any record that does not have a value in
the CURRENT_BALANCE column. Exclude any record that is blank or null.

I know I'm just getting my And/Or mixed up. Hopefully this helps and
clarifies a bit more as to what I'm trying to do. Thank you so much Conan.

Conan Kelly said:
BBAL20,

Hopefully this will help you out:

When ever you are excluding (<>) multiple values in a query, you have to
use
AND.

Think of it this way:
--(([200712].CHD_EXTERNAL_STATUS)<>"Z") OR
(([200712].CHD_EXTERNAL_STATUS)<>"C")
--Lets say one record has a C in this field. It would be excluded with
(([200712].CHD_EXTERNAL_STATUS)<>"C") alone, but because you are using OR
with (([200712].CHD_EXTERNAL_STATUS)<>"Z"), it is included. It is kinda
hard to explain in plain english, but I'll give it a try. A record with
a C
will be excluded with <>"C", but because you are using OR <>"Z", a C is
not
a Z......because a C <> Z, the C will be included with OR <>"Z"
--(([200712].CHD_EXTERNAL_STATUS)<>"Z") AND
(([200712].CHD_EXTERNAL_STATUS)<>"C") will exclude all records with C's
or
Z's in this field.

Also, I'm not too familiar with using "NOT IN ("C","L","U")", but I'm
assuming that you are trying to exclude all C's, L's, & U's......so I'm
guessing that....

((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U"))

....is the same thing as...

(([200712].CHD_EXTERNAL_STATUS)<>"C") AND
(([200712].CHD_EXTERNAL_STATUS)<>"L") AND
(([200712].CHD_EXTERNAL_STATUS)<>"U")

If that is so, then correct me if I'm wrong, but couldn't...

((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U"))

....be simplified to...

((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U","Z"))

Just to clarify, you are trying to EXCLUDE:
--the 17 portfolio numbers listed
--also any records that have an external status of Z, C, L, or U
--also any records that have a NULL balancce.

If that is the case, then change all of your OR's to AND's.

Concerning the last item, you want to include zero balances? If that is
the
case, I think you can eliminate (([200712].CURRENT_BALANCE)=0)). If
balance
= 0, then it is not null and zero-balance records will be included with
((([200712].CURRENT_BALANCE) Is Not Null)) alone.

Please reply with more information if my assumptions are incorrect, i.e.
what you want your results to be and what they actually are.

HTH,

Conan







I'm trying to build this query that has several and or statements in
the
WHERE clause. For some reason, Access does not recognize every filter
and
gives me exactly what I'm trying to filter out in the WHERE clause. Is
there
a way to make this query work using only one query or do I have to
build
several queries on top of another to get the results I'm expecting.
I'vw
pasted the Access SQL statement below for your review. Thank you in
adavance
for your help and support.

SELECT [200712].CHD_ACCOUNT_NUMBER, [200712].CHD_AGENT_BANK,
[200712].Channel, [200712].CHD_EXTERNAL_STATUS,
[200712].CURRENT_BALANCE,
[200712].CHD_CREDIT_SCORE, [200712].CHD_CREDIT_LINE,
[200712].PropAdrState,
[200712].IMB_LOAN_NUMBER, [200712].Loan_Doc, [200712].CHD_PORTFOLIO_NO,
[200712].CHD_UD_RPT2, [200712].Loan_Purpose, [200712].CHD_UD_RPT3,
[200712].NO_DAYS_DELINQ, [200712].DATE_OF_DATA, [200712].OccupancyType,
[200712].FICO_Bands, [200712].CLTV_Bands, [200712].DQ_Status,
[200712].Business_Units, [200712].Channel2, [200712].Portfolio1,
[200712].Portfolio2, [200712].ConduitFlag, [200712].CHD_OPEN_DATE,
[200712].Vintage, [200712].Quarter, [200712].CURRENT_UPB_AMT,
[200712].UP_INT_CHARGED_AMT, [200712].UP_FINANCE_CHARGES,
[200712].SellerLoanNum, [200712].CHDLS_BILLED_PAY_DUE,
[200712].CHD_CTD_UNPAID_BPD, [200712].CHDHD_PAYMENT_HISTORY,
[200712].CHDHD_AMT_LAST_PAYMENT, [200712].CHDHD_DATE_LAST_PAYMENT,
[200712].CHD_PRINCIPAL_NAME, [200712].CHD_SOC_SECURITY_NO,
[200712].CHD_SPOUSE_NAME, [200712].CHD_STATE, [200712].CHD_ZIP_CODE,
[200712].CHD_UD_GEO_CODE, [200712].CHD_MISCELLANEOUS_FIELD_6,
[200712].CHD_UD_RPT1, [200712].CHD_MISCELLANEOUS_FIELD_12,
[200712].CHD_MISCELLANEOUS_FIELD_11,
[200712].CHD_MISCELLANEOUS_FIELD_10,
[200712].CHD_CRRN_ANNL_CASH_RT, [200712].CHD_MISCELLANEOUS_FIELD_9,
[200712].CHD_MISCELLANEOUS_FIELD_5, [200712].CHD_MISCELLANEOUS_FIELD_3,
[200712].CHD_NEW_XREF_NO_1, [200712].CHD_NEW_XREF_NO_2,
[200712].CHD_UPC_11,
[200712].CHD_MISC_FIELD_13_TXT, [200712].CHD_CYCLE_CODE_99,
[200712].CHD_DATE_STATUS_CHG, [200712].LTV
FROM 200712
WHERE ((([200712].CHD_PORTFOLIO_NO) Not In
("00002","00003","00010","00012","00013","00014","00011","00004","00005","00006","00007","00008","00009","00020","09996","09997","09998")))
OR ((([200712].CHD_EXTERNAL_STATUS)<>"Z")) OR
((([200712].CHD_EXTERNAL_STATUS) Not In ("C","L","U")) AND
(([200712].CURRENT_BALANCE)=0)) OR ((([200712].CURRENT_BALANCE) Is Not
Null));
Click to expand...
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

try:

WHERE NOT([200712].CHD_PORTFOLIO_NO
IN(('00002','00003','00010','00012','00013','00014','00011','00004','00005','00006','00007','00008','00009','00020','09996','09997','09998')
OR [200712].CHD_EXTERNAL_STATUS = 'Z' OR [200712].CHD_EXTERNAL_STATUS
IN('C','L','U')) AND ( [200712].CURRENT_BALANCE = 0 OR [200712].CURRENT_BALANCE
IS NULL);
Click to expand...


My mistake, there was an extra parenthesis:

WHERE NOT([200712].CHD_PORTFOLIO_NO
IN('00002','00003','00010','00012','00013','00014','00011','00004','00005','00006','00007','00008','00009','00020','09996','09997','09998')
OR [200712].CHD_EXTERNAL_STATUS = 'Z' OR [200712].CHD_EXTERNAL_STATUS
IN('C','L','U')) AND ( [200712].CURRENT_BALANCE = 0 OR [200712].CURRENT_BALANCE
IS NULL);

This shows zero- and null-balance records.

Adding a NOT after AND in 4th line shows all records with a balance.
 

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.

Ask a Question

Similar Threads

Selsct Query Problem 3

Top