1 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Number of records in a survey

I have a survey of 311,899 records. I know this because it has just been imported from another copy of R6 and I checked the size of the taxon occurrence file in the zipped access database. The report wizard gives the same number of records, so that's fine. However, xml reports produce a list of 312,242 records!

The selection in a typical xml report is

WHERE TDET.Preferred = 1 
AND TOCC.Checked = 1
AND (TOCC.Verified = 0 or TOCC.Verified = 2)
AND TOCC.Zero_Abundance = 0
AND (Ln1.Preferred = 1 or Ln1.Location_key is null)   
AND  S.Spatial_ref_system = 'OSGB'
AND SV.SURVEY_Key = '%s'

This has happened before and has been bugging me for years. Presumably a few records are being duplicated, but how?

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

2 Reply by sallyrankin

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

Re: Number of records in a survey

Do you use Index_taxon_name in the xml report? This can cause double counting. If your system contains user added taxa and both scientific and common names have been entered the records for them get counted twice. Also there are a small number of Taxon_list_item_keys in Index_taxon_name that have 2 actual names. These also cause double counting. The test to see if Index_taxon_name is the culprit is to run ‘St03 Number of observations per species’ (Reports – Run – System Reports – Statistics) and copy the results to Excel. If you total the count column and it comes to more than the number of species observations on the Welcome screen (Help – Summary Info) then Index_taxon_name is the culprit. How many observations does ‘St01 Number of species observations per survey’ say are in the survey?

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

3 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Number of records in a survey

Thanks, Sally.

It wasn't index_taxon_name,St01, St03 and the welcome screen all gave the same number of records, but on further investigation it turned out that 12 locations had two preferred location names and it was their records that were being duplicated.

This seems to have happened when the survey was deleted and reimported. The locations already existed on my system, but my colleague had changed their names on his by adding new names. These were imported to my system with the records, giving each location two preferred names. As the import was done using All Latest I'm not sure how this happened, shouldn't the old location names also have been reimported with the preferred flag set off?

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

4 Reply by sallyrankin

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

Re: Number of records in a survey

The sort of scenario that can lead to locations having 2 preferred names is:
•    Add a name to a location that is present in 2 copies of Recorder 6 and make it the preferred name – at this point the original name is unticked so that it is no longer the preferred name
•    Export data including the location with the new preferred name from one copy of Recorder 6 and import it into the other – if All Original or All Latest is selected then the original entry in the Location Name table doesn’t get over written so it remains flagged as the preferred name but the new entry is added to the table and is also flagged as the preferred name, hence 2 preferred names. The Changed_by and Changed_date don’t get updated on the original Location Name when the new name is flagged as preferred so All Latest doesn’t detect the change.

This could be corrected by redoing the import and selecting Accept Import for any original Location Names that have new names then All Original or All Latest and Do Import although this may not be practical if there are a lot of new names. All Imported could be used instead but it may cause changes to be over written.

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