MIT Sea Grant Center for Coastal Resources

Massachusetts Bay Document Database

History of Database | Data | Changes Log | Known Problems / Future Development | Description of Parts

History:

The Massachusetts Bay Document Database is a modification of a Microsoft Access database received from the USGS in Woods Hole, MA. The primary author of that database was Ed Roworth who has since left the USGS. As far as we can ascertain, the database Ed developed was a modification of a database developed by Dylan Scott at the EPA MassBays Program that was coded directly (it didnŪt use a commercial database package) and distributed on 3.5" floppy disk.. This progression of changes explains why the database is sometimes cumbersome to work with. It was decided that although the current database isnŪt as wonderful to work with as one would wish, saving the data contained in the 1600 records that existed in previous versions was paramount.

Back to top

Data:

This database contains references relating to two separate but related topics. The first topic is anything related to the Massachusetts Bay and Boston Harbor Marine System. This includes references to contaminated sediments, policy decisions, toxics, anadromous fish runs, current modeling, etc÷ The second topic is sediment capping and is not limited to the Massachusetts Bay region. This combination of topics is a result of a research emphasis at MIT on sediment capping and the use of Boston Harbor for field data. Please note that this data has not been through a QA/QC process. While attempts have been made to check the database for glaring errors we cannot be responsible for any errors it contains. We would appreciate bringing an problems or errors to our attention.

Back to top

Changes:

Attempts were made to develop a better database structure while retaining all of the information of the previous versions. The current version is the best result obtained. The version Ed Roworth developed consisted of only four data tables: documents, authors, geocodes, and keycodes. Significant changes include the addition of data entry forms, three document reports, reports for the keycode and geoarea abbreviations, and a (hopefully) easy to use menu system for navigation around the database. All of the original data was maintained in its native form (e.g. fields were not altered). However, several fields were added for use in the MIT community such as whether the documents are available locally and who has them. Data added by MIT starts at document record number 1576.

Back to top

Known Problems / Future Development:

During the development of the current database several problems were encountered. Most of these problems result from the methods by which the original data was stored and retrieved. Many of the problems resulted from the use of lookup functions to combine the data from tables rather than simply linking the various tables directly. Currently the only outstanding problem is a result of this occurrence and is the inability to sort records by author which have been filtered on the author field. It is this problem which forced the inclusion of two sets of buttons for displaying reports on the Query / Filter Records Form (ŽBooks: Filter by FormŪ). When a filter is applied to the author field, reports must be sorted by date instead of author. Otherwise reports are sorted by primary author and then date. If an attempt is made to open the reports designed for use with filters NOT using the Author field when in fact the author field was used in a filter a run-time error will occur (#3079). At this point the best way to proceed is to click on ŽEnd and select the other report button. As far as we can tell, this run-time error occurs because the lookup function to author in the filter conflicts with the sorting of the records on the field ŽAUTHOR1Ū which is included on the report but is not visible.

The ŽAUTHOR1Ū field is included because attempts made to sort on the lookup field ŽauthorlookupŪ result in a sort being performed on the ŽauthorIDŪ field of the author table instead of on the primary author. By including the ŽAUTHOR1Ū field sorts can then be performed on the primary author using that field. It is important to note that the first records in the author table are already sorted by primary author (ŽAUTHOR1Ū) and thus appear to sort correctly when using the ŽauthorIDŪ field. However, the records towards the end of the database are not sorted by primary author. Thus any attempts to sort on records should be verified by examining records from the beginning, middle and end of the sort. This problem is most likely the result of importing the data from the EPA code into Access and then not making an allowance for further data entry.

It is anticipated that this problem can be eliminated by someone with an extensive knowledge of Microsoft Access and Visual Basic. At this time the problem is not deemed to be sufficiently large to warrant spending large amounts of time on it.

One last small problem results from the fact that the database was developed for systems running at a monitor resolution of 1024x768. Use of a lower resolution will result in tables extending off of the right and bottom sides of the screen. A future update will most likely be made that will resize the tables to eliminate this problem.

Back to top

Descriptions of Database Parts:

The database is made up of 8 tables, 1 query, 6 forms, 8 reports, and 16 macros. The following is a list of items in each category and a short description of each item.

Tables:

  • author - a unique listing of combinations of authors (up to six) in the order cited.

  • Books - contains all document information except for author, geocode, and keycode information which is looked up from other tables.

  • GEO_AREA - relates a short code for a geographic area to the full name of that area.

  • geoocde (spelling error intentional) - a unique listing of all geographic areas that are relevant to a document.

  • KEY_WORD - relates a short code for a keyword to the full description of that keyword.

  • keycode - a unique listing of all keywords that are relevant to a document.

  • MASSBAY_OLD - as far as I can tell this is the original data from the EPA database. It has not been modified or added to in any way.

  • Switchboard Items - a listing of menu items for the switchboard.

Queries:

  • Query1 - the query generated by the filter / search form for use with reports that will contain filtered records.

Forms:

  • Authors - this form is for the input of author data.

  • Books - this form is for the input of document data.

  • Filter Books - this form is for use in searching the data base. All fields are locked on this form to prevent data from being changed inadvertently during the search process.

  • GeoCodes - this form is for the input of geocodes.

  • KeyCodes - this form is for the input of keycodes.

  • Switchboard - this form is for the display of the menu box.

Reports:

  • Author Bibliography - Bibliography report for use with filters using the author field

  • Author Short Report - Short report for use with filters using the author field

  • Author Full Report - Full listing of document information for use with filters using the author field

  • Bibliography - Bibliography for use with filters NOT using the author field

  • Short Report - Short report for use with filters NOT using the author field

  • Full Report - Full listing of document information for use with filters NOT using the author field

  • GEO_AREA - report listing full descriptive locations and their short code identifier

  • KEY_WORD - report listing full descriptive keywords and their short code identifiers

Macros:

  • AlignSwitchBox - aligns menu box in upper left hand corner

  • ApplyFilterSort - applies a filter to the Filter by Form window

  • ClearFilter - clears the current filter in the active window

  • Close Filter Books - closes the Query / Filter Records windows and toolbars

  • CloseAllEditForms - closes all of the Edit Document forms

  • EditGeoAreas - opens the table for entering / editing GeoAreas and their short codes

  • EditKeyWords - opens the table for entering / editing KeyWords and their short codes

  • FilterByForm - this starts the Filter by Form command in the active window, clears the filter, and opens the custom filter toolbox (utility1)

  • NewFilter - this starts the Filter by Form command in the active window and opens the custom filter toolbox (utility1). It does not clear the current filter.

  • OpenAuthors - this opens and/or aligns the form for entering / editing author data

  • OpenBooks - this opens and/or aligns the form for entering / editing document data

  • OpenFilterBooks - this opens and/or aligns the form for searching document data

  • OpenGeoCodes - this opens and/or aligns the form for entering / editing geocode data

  • OpenKeyCodes - this opens and/or aligns the form for entering / editing keycode data

  • RefreshRecords - updates the current set of records to reflect changes in the database

  • SwitchBoxLocation - unused (?) macro that aligns the switchbox and closes the Form Design toolbox.

Back to top

Return to the main MassBay / MIT Bibliography page

 

:: Home :: MIT Sea Grant :: Site Map :: Contact ::
  this page last updated on: 7 December, 2009