Query Problem

[Guest]

Here's a bit of one table:

CAID AssetID SecNm Ex Type
2006050505 000360206 AAON INC 5/5/2006 F
2006079369 000360206 AAON INC 5/6/2006 F

Here's a bit of the other table (with some feilds taken out for space):


AssetID AType Ex Pay Src AnDt
000360206 Cash Dividend 5/5/2006 7/3/2006 CIT 4/28/2006
000360206 Cash Dividend 5/5/2006 7/3/2006 MEL 5/2/2006
000360206 Cash Dividend 5/6/2006 7/3/2006 CIT 4/29/2006

Here's the query:

SELECT TblBkData.AssetID, TblBkData.Ex, TblBkData.Rec, TblBkData.Pay,
TblBkData.Tax, TblBkData.GRate, TblBkData.Ctry, TblBkData.Curr
FROM TblFidNCS, TblBkData
WHERE ((TblFidNCS.AssetID=TblBKData.AssetID
AND TblFidNCS.Ex<>TblBkData.Ex))
ORDER BY TblBkData.Ex DESC;

Here's the query result:


AssetID Ex Rec Pay Tax GRate Ctry Curr
000360206 5/6/2006 5/7/2006 7/3/2006 0.23 0.2 GER EU
000360206 5/5/2006 5/7/2006 7/3/2006 0.23 0.2 GER EU
000360206 5/5/2006 5/7/2006 7/3/2006 0.23 0.2 GER EU

What I would like to see is nothing come up in the result here. I only want
to see a result when there is a record in the second table with an ex date
that does not match the ex date in the first table. FYI, this deals with cash
dividends. A company can report a dividend in May and then in June for the
same AssetID. If there is data in the second table, but not in the first this
will serve as a warning to update our table. I believe that when we update
the first table this should make the query produce no result, but as you can
see it is giving a result.

 

[Guest]

Try this:

SELECTAssetID, Ex, Rec, Pay, Tax, GRate, Ctry, Curr
FROM TblBkData
WHERE NOT EXISTS
(SELECT *
FROM TblFidNCS
WHERE TblFidNCS.AssetID = TblBkData.ASSetID
AND TblFidNCS.Ex = TblBkData.Ex)
ORDER BY Ex DESC;

Ken Sheridan
Stafford, England

 

[Guest]

Hello Ken:

Thank you very much! I was completely lost on this one.

Robert