1 Topic by HarryClarke

  • HarryClarke
  • Veteran
  • Offline
  • From: Surrey
  • Registered: 11-10-2011
  • Posts: 99

Topic: Re-organising records into different Surevys

I want to re-organise my records which are currently in one large survey which has become unmanageable into a survey for each year. I can do this manually by dragging the records from one survey to another, but there are too many records. Is there a way that this can be done with a batch command?
What I want to achieve is

All records from 01/01/2000 to 31/12/2000 from Survey A to Survey B
All records from 01/01/2001 to 31/12/2001 from Survey A to Survey C
All records from 01/01/2002 to 31/12/2002 from Survey A to Survey D
etc.

Harry

Harry Clarke
Surrey County Butterfly Recorder

2 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: Re-organising records into different Surevys

I too would find this very useful........

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

3 Reply by RobLarge (edited by RobLarge 24-01-2014 14:02:42)

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Re-organising records into different Surevys

Here's how I would do it (maybe not the recommended way though).

Back up the database, then in the linked access database create a new query design & switch it to SQL view
Then paste in the following query

UPDATE SURVEY_EVENT
SET
SURVEY_EVENT.SURVEY_KEY = "DSSXXXXX00000002",
SURVEY_EVENT.CHANGED_DATE = Now(),
SURVEY_EVENT.CHANGED_BY = "DSSXXXXX000000NN"
WHERE (((SURVEY_EVENT.SURVEY_KEY)="DSSXXXXX00000001")
AND
((SURVEY_EVENT.VAGUE_DATE_START)>=12345
And (SURVEY_EVENT.VAGUE_DATE_START)<34567));

Replacing
DSSXXXXX00000002 with the survey key of the survey you want to move the records to (highlight the destination survey in the observation hierarchy and click the metadata (i) button)
DSSXXXXX00000001 with the survey key of the survey they are coming from (as above, but the source survey)
DSSXXXXX000000NN with the name key of the user who is running the query (highlight the user in the Individuals window and click (i))
and 12345 and 34567 with the vague date values corresponding to the cut-off dates you have chosen (the easiest way to find these values would be to create dummy survey events with the specified dates & then look in the SURVEY_EVENT table in access, at the most recently created event (usually at the bottom of the table) & take the number in the VAGUE_DATE_START field)

Run the query and all the events samples and records in the source survey with start dates falling between the specified values will move to the destination survey (it also records that you made the change and when you did it).

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

4 Reply by RobLarge (edited by RobLarge 24-01-2014 14:03:59)

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Re-organising records into different Surevys

Of course you could use a very similar query in SQL server management studio, or wrap it up as a batch update file, but this looks like a one-off. Unless you forsee wanting to move blocks of data between surveys, based on date ranges again in the future, I wouldn't think it was worth making a batch update.

Also worth noting that it uses vague date start as the test of whether to move or not. That means that records with a date range which straddles the upper date will move, while those which straddle the lower date will not.

I should also point out that this method sidesteps Recorder validation, so it is possible to move records into a survey even if their grid refs lie outside any bounding box defined for the destination survey. Similarly if record dates lie outside the date range of the destination survey, or that survey is closed to new records, they will still be moved. Such problems will only become apparent when you next re-validate all records.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website