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

[Phil Davis]

Hi, in MS Access, I am looking for an efficient way of returning records where field values in one table are NOT present when compared to another table. I can do this using NOT EXISTS or NOT IN but they take for ever to run.

I've been looking a LEFT and RIGHT JOINs but have destroyed too many brain cells in the process and am now totally confused !

I have two tables (that describe Bird Populations as it happens!).

The relevant fields in the main Bird Sightings table (Table 1) are SPECIES, SQUARE and TETRAD which describe very many bird species sightings and the 2k geograpical squares (Square and Tetrad) they were seen in.

Table two contains fields SQUARE, TETRAD and a FLAG that lists all possible SQUARES and TETRADs in and around the County where a Y in the flag field denotes the tetrad being wholly within the county boundary.

I'd like to be able to select SPECIES, SQUARE and TETRAD identifying those SQUAREs and TETRADs where the Species has no sightings (for FLAG "Y" Tetrads only).

Could anybody suggest the most efficient SQL to do this please?

Many thanks in anticipation
Phil Davis

[zmbd]

You're lacking a primary key in both tables.
Please confirm the relationship between the two tables would be on the fields [square] and [Tetrad]? There is no real relationship given... we can only guess.

What does Table_one store in the fields [square] and [Tetrad]?

What does Table_two store in the [square] and [Tetrad] fields?

-z

[Rabbit]

If you mean you want to find all locations where there have been no sightings whatsoever, then a simple left/right outer join is all you need where the other side is null.

But if you mean a listing of locations where there have been no sightings by species, then you need a third species table, cross joined to the locations and then left joined to the sightings table where the sightings table is null.

[Phil Davis]

Hi zmbd, many thanks for your reply. I guess you've deduced there is no primary key - your right! - by the time taken to process a NOT EXISTS or NOT IN clause?

Table 1 Square and Tetrad fields contain the Ordnance Survey 10k square id (eg SP00, SU05, ST69, SO50) and Tetrad id (a single character A-N or P-Z) that identifies a 2k square piece of ground within the UK.

Tables 2 contains the same content with all possible combinations (for Gloucesestershi re) which is 683 2k squares wholly contained in Glos - marked with a "Y" flag - and hundreds of thousands of all sightings including thousands of sightings for certain commoner species (eg Blackbird).

I didn't assign a primary key to table1 because there is virtually no uniqueness in any field of table 1 but I guess I could make up a tetradid field (eg SP00A) in table 2 all of which would be unique. I'm giving away my lack of database design knowledge here probably!?

[Phil Davis]

Hi Rabbit, many thanks for your reply also. Following on from my response to zmbd, my requirement is for the second of your scenarios (a listing of locations where there have been no sightings by species).

I follow the idea of the null field but after many syntax issues I gave up thinking it may have been an Access restriction.

Would you be able to describe what the third table would contain in terms of fields and an SQL model to show how it all hangs together please? My brain is fryed !!

[Rabbit]

The third table would contain all distinct possible combinations of square and tetrad.

If you post the code you tried along with the error, I'd be able to help. But without seeing any of that, I wouldn't be able to tell you where the code went wrong.

[zmbd]

not enough information yer

Table two still seems to be your issue...

You need a way to tell me what species is being counted in table two.

Please, post us about the first three or four records from table
two.

Now let’s look at that square and tetrad...
Is the "square" a unique property of the area surveyed?
Is the "tetrad" a unique property of the "square"?
If so, then the [square]+[tetrad] is potentially a primary key.... that is... if I take "SU005" and "N" against a survey map, I should find that area once and only once.

-z

[Phil Davis]

Hi Rabbit and zmbd, I'll try to answer both your posts in one.

Rabbit, Table 2 already contains all possible combinations of Square and Tetrad (within Gloucestershire ) 683 of them. I have recreated the LEFT JOIN code I used and overcome the syntax error! However it now produces zero records where desired result would be one record containing:

BLACKBIRD ST69 J

SELECT [Atlas Field note book].Species, [Atlas Field note book].Square,[Atlas Field note book].Tetrad

FROM [Atlas Field note book] LEFT JOIN Glos_Tetrads ON (([Atlas Field note book].Square = Glos_Tetrads.Square) AND ([Atlas Field note book].Tetrad = Glos_Tetrads.Tetrad))

WHERE [Atlas Field note book].Species = "Blackbird" and [Atlas Field note book].Square IS NULL;

zmbd, the table extracts are here:

Table 1 Actual Name [Atlas Field note book] extract

Species Square Tetrad
Golden Plover SO70 C
Mute Swan SO70 C
Blackbird SO51 X
Blackbird SO70 N
Blackbird SO70 N
Herring Gull SO70 N
Herring Gull SO80 X


Table 2 Actual Name [Glos_Tetrads] extract

Square Tetrad Tetrad Flag
SO50 G Y
SO50 K Y
SO50 L Y
ST69 J Y
SU19 T Y
SU19 U Y
SU29 E Y
SU29 J Y

So Table 1 ([Atlas Field note book]) contains all of the sightings and Table 2 is purely a list of all possible Square and Tetrad combinations.

Square+Tetrad is a unique combination defining the Ordnance Survey co-ordinates of each 2k square of the UK (or British Isles?) so SU05N in your example is a unique location.

[Phil Davis]

I'll try the code and tables again with proper formatting

SELECT [Atlas Field note book].Species, 
   [Atlas Field note book].Square,
   [Atlas Field note book].Tetrad
FROM [Atlas Field note book] 
   LEFT JOIN Glos_Tetrads 
   ON (
   ([Atlas Field note book].Square = 
      Glos_Tetrads.Square)
   AND ([Atlas Field note book].Tetrad =
      Glos_Tetrads.Tetrad)
       )
WHERE [Atlas Field note book].Species =
    "Blackbird" 
    AND [Atlas Field note book].Square IS NULL;

Table 1 Actual Name [Atlas Field note book] extract

Species          Square     Tetrad
Golden Plover    SO70        C
Mute Swan        SO70        C
Blackbird        SO51        X
Blackbird        SO70        N
Blackbird        SO70        N
Herring Gull     SO70        N
Herring Gull     SO80        X

Table 2 Actual Name [Glos_Tetrads] extract

Square        Tetrad        Tetrad Flag
SO50               G               Y
SO50               K               Y
SO50               L               Y
ST69               J               Y
SU19               T               Y
SU19               U               Y
SU29               E               Y
SU29               J               Y

[Rabbit]

If you're trying to get everything from Glos_Tetrads, then you need to either reverse the order in the SQL or use a right join. Then to limit to those that don't exist in [Atlas Field note book], you need to return only those where the key field in [Atlas Field note book] is null, which you have in your WHERE clause. However, if it doesn't exist in [Atlas Field note book], then species will be null as well. You can't have a record that doesn't exist and exist at the same time, which is what you've tried to do. So if you're trying to limit it to Blackbird, then you need to either include it as a condition in the join or subquery for those records first.

[Phil Davis]

Hi Rabbit, I should have said, a RIGHT JOIN produced zero records with the code I used above also.

Following your advice (I think) I just tried the following:

SELECT [Atlas Field note book].Species, [Glos_Tetrads].Square, [Glos_Tetrads].Tetrad

FROM Glos_Tetrads LEFT JOIN [Atlas Field note book] ON (Glos_Tetrads.Tetrad=[Atlas Field note book].Tetrad) AND (Glos_Tetrads.Square=[Atlas Field note book].Square)

WHERE [Glos_Tetrads].[Tetrad Flag]="Y" And [Atlas Field note book].Species Is Null

GROUP BY [Atlas Field note book].Species, [Glos_Tetrads].Square, [Glos_Tetrads].Tetrad;

I don't really want to restrict it to Blackbird, that was thrown in to limit the processing for this exercise, although this latest code attempt didn't take long to produce zero records!!

I actually need those Square and Tetrad combinations that are missing for each species in the Atlas Field note book.

If you don't mind and it's easy for you to do, could you provide the SQL that would work for what I'm trying to achieve please? I feel I'm missing some fundemental understanding somewhere along the line.

Cheers, Phil

[Rabbit]

If you don't want to limit it to a single species, then you will need to create that third table I mentioned in post #3 and #6. Once that's created, you can cross join it to your Glos_Tetrads table and then do your left join to the [Atlas Field note book].

In the end, it would be something like this:

SELECT *
FROM locations, species
   LEFT JOIN sightings
   ON  locations.location = sightings.location
   AND species.specie = sightings.specie
WHERE sightings.specie IS NULL

The locations / species cross join will create all possible combinations of location and species, the left join with the WHERE clause will eliminate any that exist in the sightings.

[Phil Davis]

Hi Rabbit, have now created a Species table (Species_Table) containing a single field - Species - and holding a record for each distinct species found in the "sightings" table ([Atlas Field note book]).

I have also combined the Square and Tetrad fields into a new field - Tetradid - in the "locations" table (Glos_Tetrads) and "sightings" table ([Atlas Field note book]).

I created the following query:

SELECT  Species, tetradid

FROM Glos_Tetrads, Species_Table
   LEFT JOIN [Atlas Field note book] 
   ON  Glos_Tetrads.tetradid = [Atlas Field note book].tetradid 
   AND [Species_Table].Species = [Atlas Field note book].Species

WHERE [Atlas Field note book].Species IS NULL;

which I think is inline with your model but I get "syntax error in JOIN operation". I tried adding [Atlas Field note book] into the FROM table list but same problem.

I'm probably being dumb but I can't see the error!

[zmbd]

ahh... I see that Rabbit has the answer:
Here's my twist using just the two tables:
Instead of hand generating the species table. I did an agregate query on the "Atlas Field note book" by species. Now I have a list of every species that has a record in that data

I then did a cross against this query and gloss tetrads...
I then did the left join against the Atlas Field Note Book on the square and species:

and you get:

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.Species = Atlas_Field_Note_Book.Species) 
   AND
   (qry1.Square = Atlas_Field_Note_Book.Square)
WHERE (Atlas_Field_Note_Book.Species Is Null);

The only hitch I have here is the "Glos_Tetra ds" so long as it's a query or an actual table in the database you should be ok.

-z

[Phil Davis]

Hi zmbd, well that has produced an interesting result rather than zero records except I'm not sure what it has returned. It returns 90k records (Species, Square, Tetrad) which if I MAKETABLE them and Group By on the Species I get 206 distinct species out of a possible 262. The commoner species such as my beloved Blackbird is not included. It seems the species that appear in virtually all locations slip the net somehow.

Unless you and Rabbit are happy to pursue this I think I may resort to a "spot" of VBA to provide the solution.

Very many thanks to you both for you're help if you want to drop it.

Best Regards, Phil