Hi Steve

Apologies for not replying sooner (and also for the extremely long post!) - I had seen the original post but hoped that someone using Recorder 2002 more regularly than myself would reply.

As far as I am aware the validation/verification was intended to work in the same way in both Recorder 2002 and Recorder 6.

The way the 'recorder skill/species id complexity' was designed to work was that each determination has a determiner and a determiner role key. Each determiner role has a numerical value - the 'validation_competency' field, so for example a 'Specialist' has a validation competency of '3' and a 'County Recorder' has a validation competency of '2'. This value is the compared with the 'validation_level' field in the Taxon_Version table (intended to be used to represent the difficulty of identifying particular taxa). For a determiner to be able to determine a taxon occurrence so that it passes validation, the validation_competency field must be greater than or equal to the validation_level field. However, as the validation_level field have never been fully populated (I believe there are only about 10 entries in the Taxon_Version table that have a value in this field) this part of the functionality has never been fully implemented and so for your purposes can be ignored.

Perhaps John from Dorset Software will be able to confirm this but what I think should happen is that changing the determination type of the 'preferred' (preferred field in taxon_determination table is set to 1) taxon determination should set the verified field in the Taxon_Occurrence table to 0, 1 or 2 accordingly (so for example a determination type of 'original' or 'observation' should set the verified field to '0', a determination type of 'confirmation' or 'validation' should set the verified field to '2' and 'invalid' should set the verified field to '1'). However, it seems that editing a determination in anyway (including adding a non-preferred determination) sets the verified field to '2' (passed validation), apart from selecting the determination type of 'invalid' which does correctly set the verified flag to '1' (failed validation). I haven't yet come across the problem though of occurrences with no measurement data being flagged as invalid.

We have reviewed this functionality in Recorder 6 and are making some much needed changes (including clarifying the terminology) in the next version released. However, that doesn't help you with your immediate problem.

'Tinkering' with the database in any way always carries risk, but you can minimise this by regularly backing up your database. I would advise to take a copy of your NBNData.mdb file and convert the copy you have made to Access 2002 (use Tools - Database Utilities - Convert database). You should then be able to create queries and edit this copy. Create a new query and in the SQL view screen copy in the following text:

SELECT TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY, TAXON_OCCURRENCE.VERIFIED, TAXON_DETERMINATION.TAXON_DETERMINATION_KEY, TAXON_DETERMINATION.PREFERRED, DETERMINATION_TYPE.DETERMINATION_TYPE_KEY, DETERMINATION_TYPE.SHORT_NAME
FROM DETERMINATION_TYPE INNER JOIN (TAXON_OCCURRENCE INNER JOIN TAXON_DETERMINATION ON TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY) ON DETERMINATION_TYPE.DETERMINATION_TYPE_KEY = TAXON_DETERMINATION.DETERMINATION_TYPE_KEY
WHERE (((TAXON_OCCURRENCE.VERIFIED)=1) AND ((TAXON_DETERMINATION.PREFERRED)=-1) AND ((DETERMINATION_TYPE.DETERMINATION_TYPE_KEY)<>"NBNSYS0000000001"));

You can view this query in the design view once created - it selects the occurrences in your database that have a verified value of '1' (failed validation) in the Taxon_Occurrence table and where the determination type for the preferred determination is NOT 'invalid'. The next step would be to update these.

The following query updates the verified field (only for the occurrences previously selected) to 0 (not validated) where the determination type of the preferred determination is 'Observation' or 'Original':

UPDATE DETERMINATION_TYPE INNER JOIN (TAXON_OCCURRENCE INNER JOIN TAXON_DETERMINATION ON TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY) ON DETERMINATION_TYPE.DETERMINATION_TYPE_KEY = TAXON_DETERMINATION.DETERMINATION_TYPE_KEY SET TAXON_OCCURRENCE.VERIFIED = 0
WHERE (((TAXON_OCCURRENCE.VERIFIED)=1) AND ((TAXON_DETERMINATION.PREFERRED)=-1) AND ((DETERMINATION_TYPE.DETERMINATION_TYPE_KEY)="NBNSYS0000000003" Or (DETERMINATION_TYPE.DETERMINATION_TYPE_KEY)="NBNSYS0000000004"));

The next query sets the verified field to 2 (passed validation) where the determination type of the preferred determination is 'Confirmation' or 'Validation':

UPDATE DETERMINATION_TYPE INNER JOIN (TAXON_OCCURRENCE INNER JOIN TAXON_DETERMINATION ON TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY) ON DETERMINATION_TYPE.DETERMINATION_TYPE_KEY = TAXON_DETERMINATION.DETERMINATION_TYPE_KEY SET TAXON_OCCURRENCE.VERIFIED = 2
WHERE (((TAXON_OCCURRENCE.VERIFIED)=1) AND ((TAXON_DETERMINATION.PREFERRED)=-1) AND ((DETERMINATION_TYPE.DETERMINATION_TYPE_KEY)="NBNSYS0000000002" Or (DETERMINATION_TYPE.DETERMINATION_TYPE_KEY)="NBNSYS0000000005"));

After you have run the update queries convert the copied database back to Access 97 and replace your old nbndata.mdb file - please make sure you keep a copy of your old nbndata.mdb file just incase there are any problems and you need to revert back to it.

I've only very quickly ran and checked these queries-  so please make a note of the number of occurrences and the keys of the entries you have changed and double check everything looks ok in Recorder.

The other thing to do is to make sure that anyone likely to be editing determinations knows of the above issues.

Hope this helps,

Best wishes,