SQL OR problem 1

[Reggie2004]

When I combine several queries with the OR command nothing happens. This is a a copy of the code.

SELECT final.[SSN P ], final.[SSN S ], final.[SP'S SEC Y97], final.[SP'S SEC Y98], final.[SP'S SEC Y99], final.[SP'S SEC Y00], final.NCCD, final.[ DOB ], final.[ DOD ], final.[ PRIMARY NAME (ENMOD) ], final.[ PRIMARY NAME CONT'D (ENMOD) ], final.[ STREET (ENMOD) ], final.[ CITY (ENMOD) ], final.ST, final.[ZIP 1], final.ZIP2, final.ZP3, final.ZP3, final.CNLY, final.C, final.YRLR, final.[ PRIOR YEAR NAME (ENMOD) ], final.PNLY, final.P, final.[XREF SSN ], final.[INOLEX XREF CD V], final.[ INOLEX XREF TIN V ], final.[INOLEX XREF CD I ], final.[ INOLEX XREF TIN I], final.[ PRIMARY NAME (IRPTR) ], final.[ PRIMARY NAME CONT'D (IRPTR) ], final.[ STREET (IRPTR) ], final.[ CITY (IRPTR) ], final.ST1, final.[ZIP 11], final.ZIP21, final.ZP31, final.TXPD, final.[#DOCS], final.[#_SUM], final.[WAGES ], final.TX_WTHLD, final.ALLC_TPS, final.INTEREST, final.MTGINTPD, final.POINTSPD, final.PRYRRFND, final.SAV_BOND, final.DIVIDEND, final.PENS_ANN, final.[IRA_CTB ], final.NONEMPCM, final.CPTLGAIN, final.RL_ES_SL, final.GRSSDIST, final.TXBL_AMT, final.FICATXWH, final.TFICAWGS, final.MEDPYMNT, final.TFICATIP, final.FICAMISC, final.DFRDCOMP, final.UNEMPCOM, final.[RENTS ], final.ROYALTES, final.MEDCREWH, final.MEDCR_WG, final.[OR_K1 ], final.GRWINING, final.STDLNAMT, final.[ORD_DIV ], final.ROIRACTB, final.SMPL_CTB, final.[FMV ], final.CD_S_SMP, final.STK_BOND, final.[ADV_EIC ], final.FISH_INC, final.OTHERINC, final.SUB_PMTS, final.GOLD_PAR, final.CROP_INS, final.TXSTTUIT, final.TX_GRANT, final.[AG_SUBS ], final.INTFORFT, final.ORISSDSC, final.BARTERNG, final.PROFLOSS, final.AMTDBTCN, final.PATRONAG, final.MSAGRDIS, final.[TX-PRD], final.[LFRZ-RFRZ], final.MODULE_BALANCE, final.[TC-150], final.TC150_DT, final.[TC150_DLN ], final.RETRCDDT, final.[TC-290], final.[TC-291], final.[TC-300], final.DC, final.[TC-301], final.DC1, final.[TC-420], final.[TC-421], final.DC2, final.[TC-424], final.PRJ, final.[TC-425], final.PRJ1, final.[TC-460], final.TC460_DT, final.[TC-462], final.TC462_DT, final.[COLL-ASG], final.[TC-530], final.CC, final.[TC-540], final.[TC-590], final.CC1, final.[TC-591], final.CC2, final.[TC-594], final.CC3, final.[TC-598], final.[TC598 DT], final.[TC-599], final.CC4, final.[TC-610], final.[TC610 DT], final.[ TC610 AMT ], final.[TC-611], final.[TC611 DT], final.[ TC611 AMT ], final.[TC-612], final.[TC612 DT], final.[ TC612 AMT ], final.[TC-670], final.[TC670 DT], final.[TC670 AMT ], final.[TC-671], final.[TC671 DT], final.[TC671 AMT ], final.[TC672 DT], final.[TC-672], final.[TC672 AMT ], final.[TC-673], final.[TC673 DT], final.[TC673 AMT ], final.[TC-678], final.[TC678 DT], final.[TC678 AMT ], final.[TC-679], final.[TC679 DT], final.[TC679 AMT ], final.[TC-922], final.[PROCESS CDS], final.[TC-960], final.[TC-961], final.[TC-962], final.[TC-976], final.[TC-977], final.TAXPRD, final.ADJRSN
FROM final
WHERE (((final.[TC-420])<>"TC-420")) Or (((final.[TC-421])<>"TC-421")) Or (((final.[TC-424])<>"TC-424"));

 

[Katy44]

What do you mean nothing happens? If you mean that all records are returned, that's because of:

WHERE (((final.[TC-420])<>"TC-420")) Or (((final.[TC-421])<>"TC-421")) Or (((final.[TC-424])<>"TC-424"));

if final = "TC-420", then it will fail the first criteria. However, it will pass the second, and as you have an "or", it will pass in general. If that's the problem, you need to replace the ors with ands. If that hasn't helped, explain exactly what (doesn't) happen.

 

[hmckillop]

I would guess that the OR is functioning exactly as expected. If you take an example I have created below:

CREATE TABLE AA_TEST
(
FIELD1 CHAR,
FIELD2 CHAR,
FIELD3 CHAR
)

INSERT INTO AA_TEST VALUES('A', 'X', 'D')
INSERT INTO AA_TEST VALUES('B', 'Y', 'E')
INSERT INTO AA_TEST VALUES('C', 'Z', 'F')


SELECT * FROM AA_TEST 
WHERE FIELD1 <> 'A' OR FIELD2 <> 'Y' OR FIELD3 <> 'F'

As each field compared represents a different row it means that all the data will be returned i.e.
The first part of the where clause excludes row1, the second clause excludes row2, and the third clause excludes row3, but if you use or it means it will return all the data.

What exactly do you want to results to be ?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Reggie2004]

What I want is... If the first criteria is not true then goto the second. I want to exclude anything that is true. The And statement only works if all criteria is true.

Is that a better explanation.

 

[Reggie2004]

I want to exclude any field that is true. If I use = then it works. But I want to use <>.

 

[hmckillop]

Try using AND instead of OR

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Reggie2004]

And only works if I want all criteria excluded.

 

[hmckillop]

Can you give a small subset of data that you have and then the expected results you require.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Reggie2004]

It is sensitive information. But let's see, in the table there are several fields (a,b,c,d,e). I am using the query to eliminate each field. If a<>"a" then it is eliminated or if a="a" and b<>"b" then it is eliminated and so on and so forth.

 

[hmckillop]

So let me get this right. What is different from that and the AND.
Back to my example:
You want to elimnate all rows where field1 is not equal to a value (e.g. 'A') that leaves row 2, 3. you then want to exclude any where field2 is <> 'Y' this part of the query returns 2 rows also (row 1 and row 3). When an or statement is applied to both these it results in rows 1, 2 and 3 being returned.
If you use an AND statement it means only row 3 will be returned.
Now from what I can make out of your explanation in your last post this is what you require - unless i am missing the plot altogether (which is a strong possibility)


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Reggie2004]

You got it. Access is one crazy creature. When I used the And statement yesterday it did not work. But today after a compact and repair (using the and statement) it works. Go figure.

 

[hmckillop]

Gald to be of help

"I'm living so far beyond my income that we may almost be said to be living apart