Thursday, November 8, 2012

Editing a database

No matter how careful you are when entering data it may be necessary to edit the database records.

Database records are edited:
  1. to maintain consistency of information in fields
  2. to correct misspelling of words or terms that need to be altered
  3. to ensure that the F7 key (and not the Enter key) has been used when an additional term is added to a list, for example in Subjects
  4. to remove spaces in records caused by use of the Enter key, particularly after data has been entered in a field leaving spare lines in the report and display forms
  5. to check for the use of multiple terms in a field if the data is to be transferred into another database structure where only one term is allowed in a field
  6. to check for multiple versions of a record to merge if information from other sources has been added to the database, for example if information from different sources has been entered initially in Excel and then transferred into the database
Editing to identify instances of numbers 1, 2, 3 and 6 can be done via the Search Screen
F3 key
In DB/TextWorks databases placing the cursor in any of the field on the Search Screen and then using the F3 key will give you a list of all the entries or terms in that field. Scrolling down the list usually shows any inconsistencies that require attention.
Examples of instances where editing may be required:
Example 1 - Error in entering data
After using the F3 key in the Accession / Registration Number field the list showed
          1     B
          1     7065
          1     B0001
          1     B0002
The entries for B and &7065 needed to be corrected
Example 2 - Misspelling
After using the F3 key in the Subjects field the list showed
          10     Australian Tessallated Tile Co
          29     Australian Tessellated Tile Co
Batch Modify option was used to correct the misspelling of Tessellated
Example 3 - Using Enter key instead of F7 for a new term
After using the F3 key in the Subjects field the list showed
          1     Schools Churches Hotels
The entry was corrected by placing the cursor in front of the second and subsequent terms and then using the F11 key
Example 6 - Merging data from several records in one field
For one project information about people originally recorded on a number of Excel spreadsheets was imported into a database. A search under Name showed that, in some cases, there were multiple records for the same person.
          4     Smith, John
          2     Smith, Thomas
Once the multiple records for each name were identified, relevant information from some fields was cut and pasted into one record the the person. Once this was done the records no longer required were deleted. 

Basic Query Screen or Add Query Box
If the Search Screen does not contain a field you want to search, either go to the Select Query Screen icon (fourth icon from the left) and choose Basic Query Screen which contains all the fields in the database
 or select the Add Query Box icon (third icon from left)
and then select the field required.

This will produce a temporary field box on the Search Screen until the database is closed.
Once the items to be edited have been located, records can be edited individually or if the same change needs to be made to a term in the same field for a group of records then the changes can be made using Batch Modify.
Batch Modify
Once the records to be edited have been selected, go to the Records menu and choose Batch Modify.
Select the field to modify and then select how the set of records will be modified. In the above example Substitute Entry is the operation chosen while Matching is selected in the Affects column. The existing term is typed in the Old Entry box while the new term is typed in the New Entry box. Click OK, read the information in the next box that comes on the screen and if the information is correct click the OK button. 
Additional examples of Batch Modify
More information about editing databases in subsequent posts.

No comments: