Discussion of Access Database to Document Bird Intake at Oil Spills

Joe Staggs

Evidence Custodian

OLE Baltimore, MD

**Database Structure**

Using Microsoft Access we have created a database template that may be
used to record, organize, and present evidence data that is collected
during an oil spill. MS Access allows a user to input data and then
display or print that data in a variety of formats: forms, data sheets,
reports, graphs, and tables. Based on the database table that we have
created, we have also designed a form that may be reproduced and
distributed to the field teams who do the actual collection of evidence.

Each record in a database represents one complete data entry. Tables,
forms, and data sheets are all linked by MS Access. In addition, data
sheets can be exported into MS Excel. Our database table consists of the
following data fields: Log ID#, Species, Species Code, Seizure Tag \#
(ST#), Item #, Location, Location Segment, Latitude, Longitude, Found
by, Date, Time, Telephone #, Arrival Status, Outcome, Comments, and
Picture. The location field will include city and state; the location
segment should include such landmarks as intersections, public
buildings, docks, etc. A data sheet print-out of the template showing
these fields is attached (attachment #1). I have also attached a page
from the data sheet view of actual data recorded during the Athos I Oil
Spill in Philadelphia (Attachment #2).

Linked to the data sheet view is the form that has been created for each
individual record. In form design view any number of custom forms may be
designed. Each form is based on the data fields associated with the base
template. I have attached a blank form template which may be distributed
to field teams with instructions on which data fields we require them to
complete (attachment #3). Attachment #4 is one record from the Athos I
Oil Spill. This demonstrates how the form appears when the data fields
are completed -- including the linking of a digital picture.

**Representation of Data**

One important aspect of MS Access is the ability to present data in an
almost unlimited number of ways. The Pivot Table aspect in Access allows
a user to select and present specific data. For example, using the Athos
I Oil Spill (data as of 12-16-04) we can print out the Log numbers,
seizure tag -- Item numbers, arrival status, and outcome for all Canada
Geese collected by the evidence unit. Attachment #5 demonstrates how the
species code field is used as a filter to select only Canada Geese (top
left corner). Access then lists only the Canada Geese and counts the
number of data entries. Looking at the last page reveals that the
evidence unit took in 190 of these birds. By moving the arrival status
column to the filter position and selecting DOA, we can now obtain a
printout and count of the forty-two Canada Geese that arrived dead. By
selecting "LIF" we can obtain a print out and count of the 148 that were
collected alive (Attachments \# 6 & #7). By moving the outcome field to
the filter position we are able to obtain the number of Canada Geese
that Died in facility (Attachment #8). By using the location field as
filter and selecting Philadelphia, we discover that forty-nine Canada
Geese were collected in that city (Attachment #9).

In our Pivot Table demonstration we only used a small number of data
fields and yet we obtained a variety of information that could be
presented for analysis. I would think that such analysis would allow the
OLE to present a more cohesive and organized case to any AUSA. The
database also has immediate use for biologists and other branches within
FWS. Although the database may be used as it is currently structured,
Special Agent Trac Huynh and I are collaborating on linking the
Longitude and Latitude fields of the database to a topographical mapping
software program. Hopefully, this will be accomplished in the near
future.

**Usage Issues**

The Athos I Oil Spill provided the first opportunity to actually field
test how the database can be used. We must remember that a database is
only as good as the data it contains. With that in mind, here are a
number of observations:

(1). In order for the database to be effective, the data must be entered
correctly, especially with regard to numbers.

(2). During the Athos I Oil Spill, data entry did not begin until
several days after the event. There was no opportunity to meet with
field teams and give proper instruction of the type of data that OLE
requires.

(3). There was no uniformity of collection and our database form was not
used. The form that was given to field teams was inadequate and was
usually not properly filled in. In many cases telephone numbers were
left blank, no locations were given, and no names of people who brought
in the evidence were given. There was no space on the form to record if
the animal was alive or dead when it was brought in to the evidence
unit. When longitude and latitude were given, some used degrees,
minutes, and seconds, others used the decimal notation, and others used
a combination. Local names, unknown to many non-local residents were
used for location found. For example, several forms were filled out with
Oldman's Creek as the only location given -- no state, no nearby city,
etc. Many of the forms were illegible and required a great deal of
deciphering. There are two Sunoco refineries in the affected area -- one
in PA and one in NJ. Yet many forms just listed the birds were found at
Sunoco. Most forms did not list the date found. Fortunately SA Sabia
created a system of file folders by date.

(4). Sometime after December 14^th^, the evidence unit stopped taking in
live birds. They were turned in to a bird rehab organization. Later,
after December 20^th^ even the DOA birds were sent to the rehab
organization. As a result our database only contains data up to and
including December 16, 2004.

(5). A written log was maintained by the evidence unit. The log was not
as extensive as the database. Due to the fact that several different
people recorded evidence over a period of several days, it is not very
uniform and some sections are difficult to read.

**Recommendations:**

(1). The individual responsible for data entry should be designated as
such and placed on call to become a member of the initial response team.
This person should be able to enter the data correctly, distribute the
evidence forms, and instruct the teams as to which fields must be
completed accurately. Two persons be designated to be responsible for
this function. The same persons should work the entire Spill Operation.

(2). The system of longitude and latitude recorded by the field teams
should be uniform. Decimal notation will probably work better with
mapping software.

(3). OLE should work in conjunction with bird and animal rehab
organizations. I question, however, the wisdom of turning all animals,
dead or alive, to such groups. This can lead to a loss of data for
animals that were returned to the wild or for very many animals that may
have died in facility. All DOA animals should be turned in to the OLE
evidence unit for proper documentation uniformity of seizure tag
tracking.

(4). The database is too large for standard floppy discs, and requires a
complex replication process when storing on a compact disc. These issue
can be lessened by using a lap-top computer in conjunction with a 512 MB
USB flash drive. The database can be copied daily to the flash drive,
and subsequently, copied to any other computer. This is very easy to do
and can even be done at home -- it will work with any computer that has
a USB port. It is recommended that USB flash drives be used in all cases
regarding the mailing of data, the storing of data, the transportation
of data, and the saving of data.

(5). The database in data sheet view should be printed at least every
three days. The data sheet view makes a very good log for any oil spill.

![](media/image1.jpeg){width="6.376388888888889in"
height="4.626388888888889in"}

![](media/image2.jpeg){width="5.717361111111111in"
height="7.875694444444444in"}

![](media/image3.jpeg){width="5.8069444444444445in"
height="7.998611111111111in"}

![](media/image4.jpeg){width="5.534722222222222in"
height="7.624305555555556in"}

![](media/image5.jpeg){width="5.534722222222222in"
height="7.624305555555556in"}

![](media/image6.jpeg){width="5.717361111111111in"
height="7.875694444444444in"}

![](media/image7.jpeg){width="6.261805555555555in"
height="8.624305555555555in"}

![](media/image8.jpeg){width="5.989583333333333in" height="8.25in"}

![](media/image9.jpeg){width="6.261805555555555in"
height="8.624305555555555in"}