Re: Select Query

On Wed, 30 Jan 2008 07:55:01 -0800, BBAL20 <BBAL20@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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));


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);

.

Relevant Pages

  • Touble With Dates
    ... The program below prints nothing when the date filter is in the where clause ... The query work fine when I take this statement out. ... my $database; ...
    (perl.dbi.users)
  • Re: Aggregate Functions and Null
    ... > preserved table using WHERE clause, ... adding the outer rows, while the WHERE is applied after. ... If you filter in the ON clause, you'll end up with ALL rows from the ... e.g., when you join Customers LOJ Orders, and want to filter customers from ...
    (microsoft.public.sqlserver.programming)
  • RE: Passing Qry as part of Rpt DoCmd.OpenReport
    ... You are, by your description, trying to filter the output of the report. ... If you create a string that is a Where Clause without the word Where, ... And yes my data query is plain vanillia with no where clause only a single ...
    (microsoft.public.access.reports)
  • Re: How to summarize recordset...Select Distinct alternative?
    ... You can loop through this one to using Filter to easily do the first ... GROUP BY groups, it doesn't filter. ... are done on the grouped records in the SELECT clause. ... where FldPage contained "Page2". ...
    (microsoft.public.data.ado)
  • Re: Filter By Calculated Field
    ... If you mean the prompt string in the Having clause, ... You can filter the total in many different ways. ... FROM [UnionSeasonCount Query] ... SELECT DISTINCT Season, MailingListID ...
    (microsoft.public.access.queries)