1 Reply by ser

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

Re: Deleting empty Survey Events

I have a large number of empty survey_events and samples caused by a MapMate import.

Using the 'Del4 Delete Samples with no Occurrences' batch update works fine to delete all the offending samples, but 'Del3 Delete Evens with No Samples' fails with the following error:

"There is an error in the SQL code. The error message is: 'The DELETE statement conflicted with the REFERENCE constraint "FK_SAMPLE_RECORDER_SURVEY_EVENT_RECORDER". The conflict occurred in database "NBNData", table "dbo.SAMPLE_RECORDER", column 'SE_RECORDER_KEY"

Most of the Events can be deletes by hand - time consuming, but possible, however a significant number cannot even be deleted via the route - they give the error on trying to delete:

"The record cannot be deleted because it has records linked to it."

I have checked:
SAMPLE
SURVEY_EVENT_OWNER
SURVEY_EVENT_SOURCES

These all have no linked records

SURVEY_EVENT_RECORDER does have linked records, but it has in cases when the delete is possible

SAMPLE_RECORDER is the culprit - it still has a recorder which is linked to one of the SURVEY_EVENT_RECORDER records, hence this is preventing the deletion - deleting these records in Access does cure the problem, but is both VERY time consuming AND liable to corrupt Recorder if I accidentally delete the wrong row!

HELP!

Craig Slawson
Staffordshire Ecological Record

ser's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Deleting empty Survey Events

You are correct that some care is needed with Survey_Event_Recorder and Sample_Recorder as it is easy to get these table out of syc using  queries. 

The situation you desribe suggests that some records have invalid links.  Survey_Event_Recorder  links to Sample_Recorder, Sampel_Recorder links to Sample, Sample links to Survey_Event, but the Survey_Event it links to is not the same one as in Survey_Event_Recorder. A situation which shouldn't exist in a database. Not sure how it can have happened.

I will work something out for you asap. Possibly the Batch Update which deletes Events with no Samples should recognise this as a potential problems and authomatically put it right. Also the problem isn't detected  n our reports which identify database problems and perhaps it should  be.

Mike Weideli

MikeWeideli's Website

3 Reply by ser

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

Re: Deleting empty Survey Events

Mike,

I can confirm, the sample DOES still exist, and is attached to another Survey Event!

I have just run a query to check for this discrepancy, i.e.
SAMPLE -to-SURVEY_EVENT link does not equal the route through recorders

I have 241 occurrences of the problem

I assume, to correct the problem, I need to set
SURVEY_EVENT_RECORDER.SURVEY_EVENT_KEY = SAMPLE.SURVEY_EVENT_KEY

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

4 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Deleting empty Survey Events

I have just discovered that there is already a version of the delete  batch update on the forum which deals with this problem as least as far as Survey_Events with no samples are concerned . http://forums.nbn.org.uk/uploads.php?file=JNCCDel3SENoSamp.xml

The Batch Update  will not fix these problems if they are on  Events which do have Sample.   
Once the Batch update is complete you use your own query or the report at http://forums.nbn.org.uk/uploads.php?file=LCSampleRecorderProblem.xml  to see if any issues remain.

Mike Weideli

MikeWeideli's Website

5 Reply by ser

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

Re: Deleting empty Survey Events

Mike,

I've downloaded the batch file and run it, without error this time and it appears to have deleted the problem Survey Events.

I have subsequently re-run the check of sample/recorder and event/recorder and there now remain 8 recorders whose sample differed from the event - I assume I will need to change these by hand

I have checked the offending Events.

Example 1: These looked fine, both events had one recorder (the same person in both cases) and both had samples with the same person as the only and preferred recorder

Example 2: This one has gone pear-shaped. One event has 2 recorders (both the same person), the other has 3 recorders (also all the same person and the same person as the other event). Each event had 2 samples of which one is always correct, whilst the second has two recorders ticked

ALL problem records originate from MapMate imports.

Craig Slawson
Staffordshire Ecological Record

ser's Website

6 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Deleting empty Survey Events

Craig

I have attempted a batch update to fix these problems. It is more complicated than many, so please take care if you try it. It will only affect the Sample_Recorder table and only those with errors. The way it works is firstly to try and find a Survey_Event_Recorder record which has the correct Survey_Event for the Sample in Sample_Recorder and the same Name_Key  as the Survey_Event_Recorder to which it is currently linked. If this works then the SE_Recorder_Key in Sample_Recorder is changed to this.

If a match as above can't be found  then a new Survey_Event_Recorder record is created for the Survey_Event of the Sample_Recorder and with the Name_Key of the Survey_Event_Recorder to which is currently linked.  The Sample_Recorder entry is then changed to point to this.

This should avoid data loss, which could potentially happen with a  delete and it should  also avoid creating two entries for the same name. It will also put the tables back in sync.

http://forums.nbn.org.uk/uploads.php?file=LCSampleREcorderProblemFix.xml

Mike

Mike Weideli

MikeWeideli's Website