Editing databases 3

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

The next stage in the life of the regional database on the library website is to transfer the records as a subset of the library catalogue. In order to do this the information in some of the fields needed to be edited.

In DB/TextWorks databases multiple terms can be added to a field using the F7 key to separate each term. Consequently the data structure created for the local history database had just the one field for Title and one field for Author. The MARC records used in the library catalogue required only one title in the Title field and one Author in the Author field with an additional field for Added Authors.

It was therefore necessary to check for records that had more than one title or multiple authors. To do this I created a series of report forms (Display menu, Design Form, then choose Blank Form) with one box for the Accession Number and the second box for Title or Author for example. In this second box I made sure that the 'make each item or entry a paragraph' option was used.


The new form was then selected as the Report form and search was made for the items that required checking. Scrolling through the list was not a particularly exciting task and amused my work colleagues when they passed my desk. However the report form worked and I was able to identify and then alter the records that needed to be changed for the next stage of the database.

Editing databases 2

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

The spaces that appear in the above image in the Author, Genre, Subjects and Comments fields are caused by using the Enter key when data is entered.

This can easily be corrected by viewing the record in the Edit form and backspacing where the spaces caused by the use of the Enter key occur.

Generally the use of the Enter key will just cause the records to look untidy but for some projects it is necessary to locate and remove all occurrences of the use the Enter key, particularly if records are to be transferred to an Excel spreadsheet or another database that accepts records in an Excel format.

We encountered this issue in the library when IT decided to publish the local history databases online using MySQL. Any records where the Enter key had been used were rejected and unlike DB/TextWorks which informs you when records are not imported the library online database didn't.

One way to check for the use of the Enter key in a group of records is to export a set of records, view the export file in Notepad and use the Find feature (under the Edit menu) to locate the > symbol which appears when the Enter key has been used.

I take a note of the records that require editing and make the corrections in the database.
Another way would be to make the alterations in the Notepad file and then import the file back into the database, making sure that the the changed records replaced the existing records.

 

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.