8. Custom queries

Below are a number of custom analysis queries that have been produced for BSBI. To use them you will need to add them to your copy of MapMate - instructions are provided on the right of this page, or you can watch the final video on the Analysis page, or see the guidance document.

Further custom queries can be found in Appendix B of the BSBI MapMate Book, as well as on the Kitenet website. If you still can't find what you need try asking for help on the MapMate email group. 

Below, the SQL code is provided for five queries:

How to install these custom queries 

1. Find the query you wish to use in the list below.

2. Highlight and then copy the query title 

3. 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>. 

4. You will be prompted for a name; paste in the name you copied in step 2. 

5. You will now see MapMate’s SQL window with two lines of text - delete all the text that is there. 

6. Now return to this web page page and highlight the SQL text (the block of text that has statements beginning SELECT, FROM, WHERE etc.), and copy this text. 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. 

7. Return to MapMate and paste the SQL text into the blank window. 

8. Make sure you save the new query - click on the yellow folder icon at the top of the window.

    Browse all records for a <1km square>

PARAMETERS [Enter a 1km square] Text;

SELECT Records.[_guk], [Taxa\Default].Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular, [Sites\Default].Name AS Site, [Sites\Default].OSGridRef AS Gridref, IIf([Sites\Default].ViceCounty>200,'H' & [Sites\Default].ViceCounty-200,[Sites\Default].ViceCounty) AS [Vice County], Records.Quantity & IIf([*Sex]<>'u',' ' & [*Sex],'') AS Quantity, IIf([Records].DateTo-[Records].Date<27,Format([Records].Date,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].Date,"yyyy"),Format([Records].Date,"mmm yyyy"))) AS [Date], Recorders.Name AS Recorder, RecordStatus.Status, Records.Comment

FROM (((((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN RecordStatus ON Records.[*Status] = RecordStatus.[_guk]

WHERE ([Sites].[OSGridref] Like [Enter a 1km square]) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],3) & '?' & Right([Enter a 1km square],2) & '?'))  Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],4) & '?' & Right([Enter a 1km square],2) & '?')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],3) & '??' & Right([Enter a 1km square],2) & '??')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],4) & '??' & Right([Enter a 1km square],2) & '??')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],3) & '???' & Right([Enter a 1km square],2) & '???')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],4) & '???' & Right([Enter a 1km square],2) & '???'))

ORDER BY [Taxa\Default].Taxon;

    Browse Records for a <1km square> in <year>

PARAMETERS [Enter a 1km square] Text, [Enter a Year] Text;

SELECT Records.[_guk], [Taxa\Default].Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular, [Sites\Default].Name AS Site, [Sites\Default].OSGridRef AS Gridref, IIf([Sites\Default].ViceCounty>200,'H' & [Sites\Default].ViceCounty-200,[Sites\Default].ViceCounty) AS [Vice County], Records.Quantity & IIf([*Sex]<>'u',' ' & [*Sex],'') AS Quantity, IIf([Records].DateTo-[Records].Date<27,Format([Records].Date,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].Date,"yyyy"),Format([Records].Date,"mmm yyyy"))) AS [Date], Recorders.Name AS Recorder, TaxonStage.Stage, Records.Comment FROM (((((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN RecordStatus ON Records.[*Status] = RecordStatus.[_guk] WHERE ([Sites].[OSGridref] Like [Enter a 1km square]) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],3) & '?' & Right([Enter a 1km square],2) & '?')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],4) & '?' & Right([Enter a 1km square],2) & '?')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],3) & '??' & Right([Enter a 1km square],2) & '??')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],4) & '??' & Right([Enter a 1km square],2) & '??')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],3) & '???' & Right([Enter a 1km square],2) & '???')) Or ([Sites].[OSGridref] Like (Left([Enter a 1km square],4) & '???' & Right([Enter a 1km square],2) & '???')) AND ((Year([Records].[Date]))=Val([Enter a Year])) ORDER BY [Taxa\Default].Taxon;


Most standard queries don’t display the determiner. This modification will do so for the standard ‘browse all records’ query. The code added to make this work is shown in bold – note the additional brackets in the FROM clause. The same modification can be made to most standard queries (to adapt a standard query, right-click on its name in MapMate, and choose Save Query As. That will put a copy of the query into the User Queries section, and you can then edit the copy):

Browse all Records showing determiner

SELECT Records.[_guk], [Taxa\Default].Code AS Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular, [Sites\Default].Name AS Site, [Sites\Default].OSGridRef AS Gridref, IIf([Sites\Default].ViceCounty>200,'H' & [Sites\Default].ViceCounty-200,[Sites\Default].ViceCounty) AS [Vice County], Records.Quantity & IIf([*Sex]<>'u',' ' & [*Sex],'') AS Quantity, IIf([Records].DateTo-[Records].Date<27,Format([Records].Date,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].Date,"yyyy"),Format([Records].Date,"mmm yyyy"))) AS [Date], Recorders.Name AS Recorder, Recorders_1.Name AS Determiner, Methods.Method, TaxonStage.Stage, RecordStatus.Status, Records.Comment

FROM ((((((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN RecordStatus ON Records.[*Status] = RecordStatus.[_guk]) INNER JOIN Recorders AS Recorders_1 ON Records.[*Identifier] = Recorders_1.[_guk];

To show the determiner only when different from recorder, in the SELECT clause replace:

Recorders_1.Name AS Determiner

with:

IIf([Recorders].[Name]<>[Recorders_1].[Name],[Recorders_1].[Name],"") AS Determiner

    

Count Records by Hectad for <year range>

PARAMETERS [From Year (yyyy)] Long, [To Year (yyyy)] Long;

SELECT [Sites\Default].[10kSquare], Count(Records.[_guk]) AS Records

FROM (Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]

WHERE (((Year([Date]))>=[From Year (yyyy)] And (Year([DateTo]))<=[To Year (yyyy)]))

GROUP BY [Sites\Default].[10kSquare]

ORDER BY [Sites\Default].[10kSquare];

    

To count species by hectad (excluding duplicate records within the hectad) we need an intermediate query. It is of no particular use on its own, but the subsequent species count query is based on it. Because this query name is referred to in the next query, it is important that the name is entered exactly as shown:

Distinct 10k Taxa for <year range>

PARAMETERS [From Year (yyyy)] Long, [To Year (yyyy)] Long;

SELECT DISTINCT [Sites\Default].[10kSquare], [Taxa\Default].Taxon

FROM (Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]

WHERE (((Year([Date]))>=[From Year (yyyy)]) AND ((Year([DateTo]))<=[To Year (yyyy)]));

Count Species by Hectad for <year range>

SELECT [C\Distinct 10k Taxa for <year range>].[10kSquare], Count([C\Distinct 10k Taxa for <year range>].Taxon) AS Species

FROM [C\Distinct 10k Taxa for <year range>]

GROUP BY [C\Distinct 10k Taxa for <year range>].[10kSquare]

ORDER BY [C\Distinct 10k Taxa for <year range>].[10kSquare];

    

Note that if a date range has been entered, it is the start date that is tested. To use the end date, replace [Records].[Date] in the WHERE clause with [Records].[DateTo]:

Browse all Records for hybrids from <year>

SELECT Records.[_guk], [Taxa\Default].Code AS Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular, [Sites\Default].Name AS Site, [Sites\Default].OSGridRef AS Gridref, IIf([Sites\Default].ViceCounty>200,'H' & [Sites\Default].ViceCounty-200,[Sites\Default].ViceCounty) AS [Vice County], Records.Quantity & IIf([*Sex]<>'u',' ' & [*Sex],'') AS Quantity, IIf([Records].DateTo-[Records].Date<27,Format([Records].Date,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].Date,"yyyy"),Format([Records].Date,"mmm yyyy"))) AS [Date], Recorders.Name AS Recorder, Methods.Method, TaxonStage.Stage, RecordStatus.Status, Records.Comment

FROM (((((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN RecordStatus ON Records.[*Status] = RecordStatus.[_guk]

WHERE ([Taxa\Default].Taxon LIKE "* x *" OR [Taxa\Default].Taxon LIKE "X *") AND (YEAR([Records].[Date]) >= [Enter Year (yyyy)])

ORDER BY [Taxa\Default].Taxon;

    

This query doesn't do anything very useful on its own, but it is needed if you want to produce maps of species richness at the 1km square level:

Sites and Species 1k

SELECT DISTINCT Records.[*Taxon], IIf((Len([Sites\Default].[OSGridRef])=5) OR (Len([Sites\Default].[OSGridRef])=6),[Sites\Default].[OSGridRef],IIf(Len([Sites\Default].[OSGridRef])=7,Left([Sites\Default].[OSGridRef],3) & Mid([Sites\Default].[OSGridRef],5,2),IIf(Len([Sites\Default].[OSGridRef])=8,Left([Sites\Default].[OSGridRef],4) & Mid([Sites\Default].[OSGridRef],6,2),IIf(Len([Sites\Default].[OSGridRef])=9,Left([Sites\Default].[OSGridRef],3) & Mid([Sites\Default].[OSGridRef],6,2),IIf(Len([Sites\Default].[OSGridRef])=10,Left([Sites\Default].[OSGridRef],4) & Mid([Sites\Default].[OSGridRef],7,2),IIf(Len([Sites\Default].[OSGridRef])=11,Left([Sites\Default].[OSGridRef],3) & Mid([Sites\Default].[OSGridRef],7,2),Left([Sites\Default].[OSGridRef],4) & Mid([Sites\Default].[OSGridRef],8,2))))))) AS 1kSquare

FROM (Records INNER JOIN [Sites\Default] ON Records.[*Site]=[Sites\Default].[_guk]) INNER JOIN [Taxa\Default] ON Records.[*Taxon]=[Taxa\Default].[_guk]

WHERE ([Sites\Default].[OSGridref] Not Like "??##?") AND (Len([Sites\Default].[OSGridref])>4);