Saturday, January 5, 2013

SP5595ish, 05/01/2013

Another day for me where the main focus was birds, though I also picked up my first moth and a harvestman. I can't write the same thing on two blogs, so this time you can catch up with these and a couple of photos on my blog.

I'm on a paltry 51 species as I write this, though I have resolutely ignored plants, trees and bushes so far - I don't think they are going anywhere fast! My 51 species includes: 32 birds, 4 slugs, 2 woodlice, 2 mammals, 2 lichens, 2 mosses, 1 fungi, 1 harvestman, 1 moth, 1 liverwort, 1 earwig, 1 worm and 1 centipede.

One for the MapMate users - it strikes me that trying to maintain a yearlist covering all taxa and (hopefully) a 1000 species is going to get a bit mind-boggling. I'm really not very good at writing SQL queries, but I seem to get by with creating useful mish-mash queries from existing ones. The following might be useful to you BUT there is a catch: I can't get this to reliably work down to 1km level at the moment so this will only be good for you if you are not also entering records from other sites within the 10km square. I am sure there must be a way to improve this, but it may be useful in the meantime. Would be good if one of the MapMate experts on this blog could have a think over a coffee and a kit-kat in a few spare moments ....

Classified Species List for <10k square> in <year> 
PARAMETERS [Enter a 10k Square - eg SP59] Text, [Enter a Year - eg 2013] Text;
SELECT [Taxa\Default].Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular, Taxa_4.Taxon AS ['Kingdom'], Taxa_3.Taxon AS ['Division'], Taxa_2.Taxon AS ['Class'], Taxa_1.Taxon AS ['Order'], Taxa.Taxon AS ['Family'], Format(Min([Date]),'dd-mmm-yyyy') AS Earliest, Format(Max([Date]),'dd-mmm-yyyy') AS Latest, Count(*) AS Records, Sum(Records.Quantity) AS Individuals
FROM ((((((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN Taxa ON [Taxa\Default].[**Parent] = Taxa.[_guk]) INNER JOIN Taxa AS Taxa_1 ON Taxa.[**Parent] = Taxa_1.[_guk]) INNER JOIN Taxa AS Taxa_2 ON Taxa_1.[**Parent] = Taxa_2.[_guk]) INNER JOIN Taxa AS Taxa_3 ON Taxa_2.[**Parent] = Taxa_3.[_guk]) INNER JOIN Taxa AS Taxa_4 ON Taxa_3.[**Parent] = Taxa_4.[_guk]
WHERE ((([Sites\Default].[10kSquare])=[Enter a 10k Square - eg SP59]) AND ((Year([Records].[Date]))=[Enter a Year - eg 2013]))
GROUP BY Taxa_4.Taxon, Taxa_3.Taxon, Taxa_2.Taxon, Taxa_1.Taxon, Taxa.Taxon, [Taxa\Default].Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular;

Opilio canestrinii


  1. Jeez, Harvestmen give me the creeps! I mean, just look at it! What a freak.

    Good SQL'ing I'd say. I use Mapmate for all my Lep records. I know I have used down to 2km squares before for atlases by adding a letter at the end of the grid reference (eg (([Sites\Default].[2kSquare])='WV48B') but I use it so infrequently that I always forget how it all works.

  2. I don't use Mapmate myself, but I do use SQL. Difficult to be sure without a good knowledge of the database, but I bet if you try something like Mark's suggestion, but just try [Sites\Default].[1kSquare])='WV4281' then you'd have a fighting chance of it working. Just have a play and see what comes out

  3. Sorry, clumsy of me. I meant


  4. I'd already tried changing the code to reflect a 1k square but it didn't work. I've forced a solution by changing the admin area for each pertinent site which I can then use in filtering.