Custom user queries

How to install these user queries

Click on the link below for the query you wish to use. This will take you to a Google docs page showing the query’s title and the SQL text needed to set it up.
1. Highlight and then copy the query title
2. Go to MapMate and use the normal method for creating customised queries: open the "Analysis" window, double-click on the "User Queries" sub-heading and choose <new user query>.
3. You will be prompted for a name; paste in the name you copied in step 1.
4. You will now see MapMate’s SQL window with two lines of text - delete all the text that is there.
5. Now return to the Google docs page and highlight the SQL text (the block of text that has statements beginning SELECT, FROM, WHERE etc.), and copy this text (the right-click Copy function doesn't seem to work on Google docs, but you should be able to use Ctrl+C to do the copy). NB that some of these queries are rather long, make sure you highlight all the text from the beginning up to and including the semicolon at the end.
6. Return to MapMate and paste the SQL text into the blank window.
7. Make sure you save the new query - click on the yellow folder icon at the top of the window.


Exporting MapMate records to Google Earth: click here for an example of the output from the query listed below.

Showing 31 items
querynoteskeyworddate uploaded
Sort 
 
Sort 
 
Sort 
 
Sort 
 
querynoteskeyworddate uploaded
Summary of records from centres with info on deletes and archived records For each CUK, shows the total number of records, the total number that haven't been deleted, and the total number that have been neither deleted nor archived analysis 12 December 2015 
Browse all records for <reference> Shows all records (with determiner) for a particular reference analysis 10 April 2015 
Browse Taxa showing synonyms Shows all names in current taxon filter, with an additional column giving the name that each synonym refers to list species 25 November 2014 
Browse records with extra detail - REVISED 2 (revised at Apr 2014 to show correct 1km squares for 12-fig and for Irish grid refs) set of 6 queries (and 6 sub-queries) - extra details include: Determiner, Reference, taxon classification, national status etc. browse records 23 April 2014 
Browse Sites Not in Use Shows all sites on your system that are NOT linked to records or recorders other 9 February 2014 
Best day for species Shows the days with the highest species totals for your current filter settings analysis 20 November 2013 
Exporting MapMate records to Google Earth Produces an output that can be used with the very useful Google Earth tools provided by Keith Balmer on the Beds Nat Hist Soc website other 20 November 2013 
Species per Year Shows the number of species recorded each year for the current taxon and geographical area filter settings. analysis 3 October 2013 
Check for incorrect 10-figure grid refs A bug in MapMate can cause sites with 10-figure grid refs to be synced inaccurately - this query allows you to see if you have sites affected by this other 13 November 2012 
Replace <species 1> with <species 2> Allows you to change ALL your records from one species name to another, for updating scientific name changes update 14 October 2012 
Find Records with tabs or line breaks in the Comments It's possible for tab or line break characters to end up in the Comments field of MapMate, which can cause problems when exporting data as text files. These queries allow you to find the records so they can be edited. other 9 October 2012 
Top 100 most abundant species A simple adaptation of MapMate’s standard “Top 20 most abundant species”. analysis 6 August 2012 
Habitats Directive Species Query and Filter to allow listing of records for Habitats Directive species, set up to facilitate JNCC questionnaire list species 7 December 2011 
Species list with details of first record Lists species with the details of the first-ever record for each on your database list species 5 November 2011 
Earliest records showing full dates Shows the record/s that are the earliest in the year that each species has ever been recorded other 21 May 2011 
Browse all records for GIS Standard browse query with X and Y coords for exporting to GIS; also includes determiner and reference fields browse records 25 January 2011 
Update site for set of records on date update query to do bulk edit of site for a set of records on a date (NB will change your records, use with caution, back-up first!) update 15 July 2010 
List Records introduced from <gen A> to <gen B> inclusive Adapted from standard query "List Records introduced at <generation>" browse records 28 June 2010 
Species League Table (10km Records) 1980 onwards Query adapted from the standard "Species League Table (10km Records)", filtered to exclude records from before 1980. analysis 6 April 2010 
Sites showing records summary This query lists all sites that match the current sites filter, and shows how many records there are at each site, and what years the earliest and latest ones are. other 11 March 2010 
Species List full details for site Requires the subqueries from "Browse records with extra detail" - lists species with classification and status list species 3 February 2010 
Browse all Records with determiner and reference Standard browse query with determiner and reference added - also a version that allows you to choose records for a particular reference browse records 1 February 2010 
Analyse species abundance trends queries to analyse trend of one species against overall numbers for all species analysis 11 January 2010 
Browse records with <any text> in the comment field  browse records 11 January 2010 
Customising queries to use 'Refers To' shows how to set up queries that use "Refers To" to show synonyms, with example SQL other 11 January 2010 
Export records for NBN Gateway for records centre/recording scheme use: exports data in format for sending to NBN Gateway browse records 11 January 2010 
record verification - set of queries lengthy document containing 14 queries (and 9 sub-queries) to assist with validating/verifying records validation 11 January 2010 
Replace <date to change> with <corrected date> update query to do bulk edit of dates (NB will change your records, use with caution, back-up first!) update 11 January 2010 
1kSquareList sub-query for listing records by 1k square - UPDATED at 14 March 2014 to allow for Irish grid references sub-query 9 January 2010 
Browse all Records for <1km-square> requires sub-query "1kSquareList" browse records 9 January 2010 
10km square list for a <vice-county> provides list of 10km squares that overlap county other 6 January 2010 
Showing 31 items
Comments