1 Topic by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Topic: MapMate style queries

I have a new user of Recorder 6 and he's asked if he can run the following 'MapMate-type' queries in Recorder, I'm afraid I've failed to be able to do the searches - can anyone come up with the report syntax for the queries? ... or point me in the direction of any existing reports

Species list for <site>
Species List for <10km square>
Species list for <VC>

Species new for <site> in <year>
Species new for <VC> in <year>
Species new for <10km square> in <year>
Species new in <VC> since <year>

Number of Species per 10km square

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

2 Reply by Samuel Millar

  • Samuel Millar
  • Samuel Millar
  • Member
  • Offline
  • From: CEDaR (volunteer)
  • Registered: 03-07-2014
  • Posts: 17

Re: MapMate style queries

Muggle answer follows...

Run the report wizard for the site/square/polygon, export the records, import to an Access table and run a query like

SELECT DISTINCT [Taxon]
FROM [Records];

and for news, run, export and import two reports for dates before and after the cutoff (say pre2016 and 2016), run the above for each, export to Excel, add a column to the 2016 species list which looks up values in the pre2016 list:

=ifna(vlookup('2016'!A2,'pre2016'!A:A,1,false), "NEW!")

and filter.

Looking forward to someone posting a far more slick XML/SQL way of doing it, I'm intrigued myself... :)

3 Reply by Samuel Millar

  • Samuel Millar
  • Samuel Millar
  • Member
  • Offline
  • From: CEDaR (volunteer)
  • Registered: 03-07-2014
  • Posts: 17

Re: MapMate style queries

Hi again Craig,
Procrasticoding FTW... :)
In SQL Server Management Studio...

SELECT itn.ACTUAL_NAME as 'Taxon',
    itn.AUTHORITY as 'Authority',
    tr.LONG_NAME as 'Rank',
    tl.ITEM_NAME as 'List'
FROM nbndata.dbo.INDEX_TAXON_NAME as itn
INNER JOIN nbndata.dbo.TAXON_LIST_ITEM as tli
    ON tli.TAXON_LIST_ITEM_KEY=itn.TAXON_LIST_ITEM_KEY
    INNER JOIN nbndata.dbo.TAXON_RANK as tr
        ON tr.TAXON_RANK_KEY=tli.TAXON_RANK_KEY
INNER JOIN nbndata.dbo.TAXON_LIST_VERSION as tlv
    ON tlv.TAXON_LIST_VERSION_KEY=itn.TAXON_LIST_VERSION_KEY
    INNER JOIN nbndata.dbo.TAXON_LIST as tl
        ON tl.TAXON_LIST_KEY=tlv.TAXON_LIST_KEY
WHERE itn.TAXON_LIST_ITEM_KEY in(
        SELECT DISTINCT itn.RECOMMENDED_TAXON_LIST_ITEM_KEY
        FROM nbndata.dbo.INDEX_TAXON_NAME as itn
        INNER JOIN nbndata.dbo.TAXON_DETERMINATION as td
            ON td.TAXON_LIST_ITEM_KEY=itn.TAXON_LIST_ITEM_KEY
            INNER JOIN nbndata.dbo.TAXON_OCCURRENCE as toc
                ON toc.TAXON_OCCURRENCE_KEY=td.TAXON_OCCURRENCE_KEY
        WHERE td.PREFERRED=1
            and td.DETERMINATION_TYPE_KEY in(
                'NBNSYS0000000002',--Validation
                'NBNSYS0000000003',--Observation
                'NBNSYS0000000004',--Original
                'NBNSYS0000000012',--Correct
                'NBNSYS0000000007',--Considered Correct
                'NBNSYS0000000011'--Unconfirmed
                --excluding 'NBNSYS0000000001',--Invalid
                --'NBNSYS0000000005',--Confirmation,
                --'NBNSYS0000000008',--Cons. Incorrect,
                --'NBNSYS0000000009',--Incorrect,
                --'NBNSYS0000000010',--Req. Confirmation
                )
            and toc.CHECKED=1
        )
ORDER BY tr.LONG_NAME, itn.SORT_ORDER
;

Still working on the location bit, but maybe there are some pieces you can use.