Help Request for an efficient alternative to "NOT EXISTS" or "NOT IN"

[Rabbit]

We can't really tell what's going on without seeing all the data from each table for at least one of the species that doesn't show up.

[zmbd]

weird result

That's interesting... worked fine on the little-bitty tables...

opps... copied and pasted the wrong one...

SELECT qry1.*
FROM 
     (SELECT Glos_Tetrads.Square, Glos_Tetrads.Tetrad, qry2.Species 
     FROM (SELECT Atlas_Field_Note_Book.Species 
          FROM Atlas_Field_Note_Book 
          GROUP BY Atlas_Field_Note_Book.Species)  
     AS qry2, 
          Glos_Tetrads)  
     AS qry1 
LEFT JOIN Atlas_Field_Note_Book ON 
          (qry1.Tetrad = Atlas_Field_Note_Book.Tetrad) 
     AND (qry1.Species = Atlas_Field_Note_Book.Species) 
     AND (qry1.Square = Atlas_Field_Note_Book.Square)
WHERE (Atlas_Field_Note_Book.Species Is Null);

you need that (qry1.Tetrad = Atlas_Field_Not e_Book.Tetrad) in there
Personally, I would have put a record_id in that glos_tetrads table... just for that reason... I hate compound primary keys.

Lines 4 thru 6 (qry2) generate the species list from the Atlas_Field_Not e_Book.Species, If you cut/paste into a new query remove the "()" and tack ";" on the end it will return a list of every species that has a record in that table. If the blackbird still doesn't show with this agregate then there's another issue and you'll have to generate the table as Rabbit suggests.

[Phil Davis]

Hi zmbd, fantastic !! Absolutely bang on. A sample shows:

Species	Square	Tetrad
Blackbird	ST69	J
Blue Tit	ST69	J
Greenfinch	SO92	V
Greenfinch	SP11	Z
Greenfinch	SP13	F
Greenfinch	SP20	D
Greenfinch	ST59	Y
Greenfinch	ST69	J
Greenfinch	ST69	Y

Just what I would have expected and it processed in a matter of a few seconds - brilliant. Thanks so much to you and Rabbit for spending so much time on this. I even think I understand the processing now.

Best Regards, Phil