Selsct Query Problem

B

BBAL20

I'm having a problem with the simple selct query pasted below. For some
reason Access is not filtering out and returning the data listed and directed
by the WHERE clause. The way I understand it, I should'nt see the "Z" code or
any of the codes listed in the CHD_PORTFOLIO_NO column, but that's not the
case. Do I need to build several queries on top of another to get the results
I expect or can this be done in one query? Thank you in advance 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));
 
J

John Spencer

Perhaps what you want is
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 Not In ("C","L","U") AND
[200712].CURRENT_BALANCE Is Not Null )

The above modification should show two sets of records. It will include
records with a Status of Z as long as their current balance is not Null that
will (or could) include records you have excluded in based on the first set
of criteria.

Perhaps someone can offer up the proper WHERE CLAUSE if you explain in words
what you are trying to acccomplish. --
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

BBAL20 said:
I'm having a problem with the simple selct query pasted below. For some
reason Access is not filtering out and returning the data listed and
directed
by the WHERE clause. The way I understand it, I should'nt see the "Z" code
or
any of the codes listed in the CHD_PORTFOLIO_NO column, but that's not the
case. Do I need to build several queries on top of another to get the
results
I expect or can this be done in one query? Thank you in advance 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...
 
B

BBAL20

It didn't work. For exmaple. I don't want to eliminate the combination of "Z"
AND the CHD_PORTFOLIO_NO any code that is Not In that statement. I want to
eliminate the "Z" code altogether or any code Not In the CHD_PORTFOLIO_NO
column altogether. One has nothing to do with the other. If Access finds a
"z" value then it needs to eliminate all records regardless of what is in the
CHD_PORTFOLIO_NO column and vice versa. The only combination that I want to
eliminate is any code Not in or not listed in the CHD_EXTERNAL_STATUS that
has a 0 value in the CURRENT_BALANCE column. I know it may sound a bit
confusing, but I definitely don't want to include everything in the AND
portion. Access would look for a combination of all those values showing up
in one record and only eliminate those. Any other suggestions?

KARL DEWEY said:
Try changing all of your 'OR' in the WHERE part of the query to 'AND'.
--
KARL DEWEY
Build a little - Test a little


BBAL20 said:
I'm having a problem with the simple selct query pasted below. For some
reason Access is not filtering out and returning the data listed and directed
by the WHERE clause. The way I understand it, I should'nt see the "Z" code or
any of the codes listed in the CHD_PORTFOLIO_NO column, but that's not the
case. Do I need to build several queries on top of another to get the results
I expect or can this be done in one query? Thank you in advance 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...
 

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

Select Query 6

Top