8. Custom queries

    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);