1 Reply by Nick

  • Nick
  • Veteran
  • Offline
  • From: Aberdeenshire
  • Registered: 11-05-2006
  • Posts: 70

Re: Queryies split by Groups

Hi

I posted a question elsewhere (http://forums.nbn.org.uk/viewtopic.php?id=64) asking for help but I guess maybe it was lost in the general comments so I hope you don't mind me breaching netiquette (slightly) and asking again.

What I want to be able to do is export our records from Recorder but sort them by group (acorn worm, alga ... two-tailed bristletail). The terms for this group seems to be referenced by various names e.g.  taxon group keys (see http://www.searchnbn.net/library/webser … Groups.csv), TaxonGroup, TaxonReportingCategory or "GroupName" (see http://forums.nbn.org.uk/recreport/index.php?id=1).

Can anyone tell me what to use (table and field name) to build our query for these groups using MS Access as I spent a lot of time trying to set up the relevant query :(

BTW someone called the Reporter relationships a "Rats nest" :)

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

Nick's Website

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Queryies split by Groups

Hi Nick,

The following query shows how you can join from Index_Taxon_Name through the recommended names, to the taxon version then taxon group information:

select top 100 itn.actual_name, itn.Common_Name, tg.taxon_group_name
from index_taxon_name itn
inner join taxon_list_item tli on tli.taxon_list_item_key=itn.recommended_taxon_list_item_key
inner join taxon_version tv on tv.taxon_version_key=tli.taxon_version_key
inner join taxon_group tg on tg.taxon_group_key=tv.output_group_key

This is Transact SQL, not Access code, so you may need to fiddle with brackets to get it working, but it should show the joins you need.  I hope this helps.

Regards

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by Nick

  • Nick
  • Veteran
  • Offline
  • From: Aberdeenshire
  • Registered: 11-05-2006
  • Posts: 70

Re: Queryies split by Groups

Hi John

Many thanks for your help. It may be a while before I can try it out but will let you know how I get on

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

Nick's Website

4 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: Queryies split by Groups

I did a little experiment, following Jon's code. I'm in the middle of producing a report which shows data input progress over the years and it occurred to me that it might be useful to display this in respect of those Taxon Groups (I've already got a similar one that works for named Surveys), so I adapted the above code in Access 2003 to come up with the following:

SELECT     TOP 100 PERCENT YEAR(dbo.TAXON_OCCURRENCE.ENTRY_DATE) AS Year, dbo.TAXON_GROUP.TAXON_GROUP_NAME AS [Group], 
                      COUNT(dbo.TAXON_GROUP.TAXON_GROUP_NAME) AS Numbers, dbo.TAXON_GROUP.SORT_ORDER
FROM         dbo.SAMPLE INNER JOIN
                      dbo.TAXON_OCCURRENCE ON dbo.SAMPLE.SAMPLE_KEY = dbo.TAXON_OCCURRENCE.SAMPLE_KEY INNER JOIN
                      dbo.TAXON_DETERMINATION ON 
                      dbo.TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = dbo.TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY INNER JOIN
                      dbo.TAXON_LIST_ITEM ON dbo.TAXON_DETERMINATION.TAXON_LIST_ITEM_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY INNER JOIN
                      dbo.INDEX_TAXON_NAME ON 
                      dbo.TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = dbo.INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY INNER JOIN
                      dbo.TAXON_VERSION ON dbo.TAXON_LIST_ITEM.TAXON_VERSION_KEY = dbo.TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN
                      dbo.TAXON_GROUP ON dbo.TAXON_VERSION.OUTPUT_GROUP_KEY = dbo.TAXON_GROUP.TAXON_GROUP_KEY
GROUP BY dbo.TAXON_OCCURRENCE.CHECKED, YEAR(dbo.TAXON_OCCURRENCE.ENTRY_DATE), dbo.TAXON_GROUP.TAXON_GROUP_NAME, 
                      dbo.TAXON_GROUP.SORT_ORDER
HAVING      (dbo.TAXON_OCCURRENCE.CHECKED = 1)
ORDER BY YEAR(dbo.TAXON_OCCURRENCE.ENTRY_DATE), dbo.TAXON_GROUP.SORT_ORDER

This gives me a simple list sorted by year, then Taxon Group (sort order) plus the number of Taxon occurrences in each of those groups. Rearranged in a spreadsheet it makes for a nice presentation.
One puzzle though. I've come up with horrendously more harvestmen than spiders and I'm sure my database is the other way around - is it my coding or are they transposed in the Taxon_group table?