1 Reply by Ian M Spence

  • Ian M Spence
  • Veteran
  • Offline
  • From: North east Wales
  • Registered: 30-06-2006
  • Posts: 193

Re: Reporting on locations that have a suite of species

Hi

I am trying to check which locations in my database have records for a suite of bird species. In theory, it shouldn't matter that not all species are seen or recorded on the same day, but that they are recorded in the same year.

Using the Report Wizard I can prepare a report that gives me all occurrences of individual species. What I cannot find how to do is to limit the search so that I only have species 'a + b + c + d' which should be a much smaller set of locations than occurrence of those species individually. I think the SQL would include:

and (species = 'a' and species = 'b' and species = 'c' and species = 'd')

In the Additional Filters screen, I think it is possible to do this by using the AND to link the desired variables to search for (for me, Taxon Common Name). What I don't understand is that when I put the condition of just species a + b, I get no locations, yet I know that there are records in the database for at least one location with those species present on the same day. Why are these records not showing? If I cannot retrieve any locations with just two species I am unlikely to get any with five species. Am I approaching my question the wrong way?

Any advice would be gratefully received. Thanks in anticipation!

Cheers, Ian

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Reporting on locations that have a suite of species

Hi Ian,

What you're trying to do won't work because the filter works on a per-record basis. That is, if you put a condition in of Taxon Common Name A AND Taxon Common Name B, you're asking for all records that have a common name of a+b in the same record, which is impossible; a record can only have one name: A OR B.

It is possible to do what you want to do, but it would require writing an XML Report. I'd suggest contacting Mike Weideli as he could probably knock something up for you quite quickly.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

3 Reply by Ian M Spence

  • Ian M Spence
  • Veteran
  • Offline
  • From: North east Wales
  • Registered: 30-06-2006
  • Posts: 193

Re: Reporting on locations that have a suite of species

Hi Charles

Mmm, so dodgy logic on my part. Blast! I see what you mean. I keep seeing mention of XML reports. I guess I ought to investigate them - Mike was  very helpful when I transferred to R6 but feel uncertain about asking for further help from him. Many thanks for the suggestion, though.

All the best, Ian

4 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Reporting on locations that have a suite of species

If you want to report on numerous species from different taxonomic groups it will probably be easier to use a rucksack to drive the report:

Create a rucksack containing the required taxa
Open it prior to starting the report
Reports – Wizard…   
Select report style – Taxon Records
Select the option(s) to restrict the report to one or more Sources, one or more Admin Areas, a Bounding Box or to one or more Polygons, if required – Next
Select a Check List – note that with a rucksack open two additional options are available: Current Rucksack and Current Rucksack (Expanded) – select one of these
Select all species by clicking the double headed arrow to move all the species from the Available box to the Selected box
Proceed as for the standard report for taxa.

I presume the same works for ‘Report about a Place’ but I haven’t checked it in the latest version of Recorder 6.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

5 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Reporting on locations that have a suite of species

Hi Sally,

Rucksacks won't do what Ian wants either. What Ian wants to do (as I understand it) is produce a list of locations that have all species in his suite recorded against each location. So, for a location to be returned in the report, it must have at least one record for each species in his suite recorded against it.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

6 Reply by Ian M Spence

  • Ian M Spence
  • Veteran
  • Offline
  • From: North east Wales
  • Registered: 30-06-2006
  • Posts: 193

Re: Reporting on locations that have a suite of species

Hi Sally

Charles' interpretation is correct.

I have tried what you suggested and got as far as not choosing to tick Admin Areas or the other options and Next. There were no extra options available and there was no way to choose the taxa I had included in my rucksack.
I can't find any way to get the two options about Rucksacks that you mention.

So far, I have had to do the sort more manually, than automatically, but selecting the taxa, creating a report with all records of those species, sorting the spreadsheet by Location then looking for those with the suite of species I wanted (between 5 and 8 species) - obviously, I had to do the counting (I can count to 8 on a good day, but with so many processes, there is scope for errors to creep in).

Cheers, Ian

7 Reply by Graham French

  • Graham French
  • Administrator
  • Offline
  • Registered: 23-05-2006
  • Posts: 430

Re: Reporting on locations that have a suite of species

Hello Ian

This may not make any sense at all, if so ignore it!. To reduce errors in your manual counting of species for each location, you could consider replacing the species in your spreadsheet with numerical values (as factors of 2) and then adding them up for each location. First species = 1, second species =2, third species = 4, fourth species = 8 and so on .... eighth species = 128. Replace all other species not wanted with 0. By making sure the cells are treated as numerical values and not text, in this way unique combinations of selected species will have unique numerical values ie first 5 species = 1+2+4+8+16 = 31, ist and 3rd species = 5, all 8 = 255.

Just a thought, as I say ignore it if this all sounds like gobble-dee-gook

Best wishes

Graham

Graham French's Website

8 Reply by Ian M Spence

  • Ian M Spence
  • Veteran
  • Offline
  • From: North east Wales
  • Registered: 30-06-2006
  • Posts: 193

Re: Reporting on locations that have a suite of species

Hi Graham

I understand what you have suggested and had not thought of such a system. I shall give it a try.

Thanks, Ian

9 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Reporting on locations that have a suite of species

Ian, did you open the rucksack before starting the wizard and did you scroll to the end of the list of checklists to find ‘Current Rucksack’ and ‘Current Rucksack (Expanded)’?

Can you use Access? If so, you could export the records found using the Report Wizard and your rucksack to an Access database and query them there. A combination of select distinct and totals queries or Graham's suggestion will probably give you the report you want. Apologies for mis-reading your requirements.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

10 Reply by Ian M Spence

  • Ian M Spence
  • Veteran
  • Offline
  • From: North east Wales
  • Registered: 30-06-2006
  • Posts: 193

Re: Reporting on locations that have a suite of species

Hi Sally

At the time I wrote, I did not know to go to the end of the list of checklists to find 'Current Rucksack', though mine was open.

I have just done this and found that I end up with the same result as if I entered all the species in the suite in the report wizard, so I get every record that has one of those species. I had wanted to be able to identify only the records for Location that had all members of the suite of species.

I have created databases in Access and can do various things but cannot create functions writing my own code. I will be able to import to Access the data exported into Excel, but I cannot find how to export the contents of the rucksack (though my suite of species is not big - only eight species so maybe I don't need to know). However, I would like to know!

All the best, Ian