BIAA Database Documentation

The Birds in Agricultural Areas database is designed in Microsoft Access and contains the following tables:

AuthorEditor – This table contains five columns of data which are:

• Author/EditorID – an autonumber field which is used to uniquely identify each author and editor name.
• CitationID – a number field which is used to associate the author or editor with the correct citation. CitationID is the primary key for the Citations Table.
• Desig – a text field which designates whether the entry is for an author or an editor.
• Primary – a text field which designates whether the author or editor is the primary (users need to know this because the documents are filed by primary author or editor last name).
• LastName – a text field which contains the last name of the author or editor.
• Initials – a text field which contains the initials of the author or editor.

BCR_Def – This table contains two columns of data which are:

• BCR_ID – a number field which contains the numbers of all Bird Conservation Regions as defined by the North American Bird Conservation Initiative (NABCI).
• BCR_Name – a text field which contains the names of all Bird Conservation Regions as defined by North American Bird Conservation Initiative (NABCI).

BirdCropAssoc

• AssociationID – an autonumber field which is used to uniquely identify each bird crop association.
• CommonName – a text field which contains the common bird name for this association. Common names are defined in the SpeciesRef table.
• CropName – a text field which contains the crop for this association. Crops are defined in the CropNames_Def table.
• Usage – a text field which describes how the bird uses the crop in this association. Usages are defined in the Usage_Def table.
• CitationID – the ID of the citation which contains the bird to crop reference.
• Comments – any special comments about this association.

CitationRelationships

• CitationRel – autonumber field used to uniquely identify each relationship.
• PrimaryCite – a number field containing the citationID for the primary citation.
• SecondaryCite – a number field containing the citationID for the secondary citation.

Citations

• CitationID – autonumber field used to uniquely identify each citation.
• Title – a text field containing the title of the citation.
• Year – a text field containing the publication year of the citation.
• VolName – a text field containing the name of journal or edited volume.
• Volume – a text field containing the volume if journal article.
• Number – a text field containing the number if journal article.
• Pages – a text field containing the inclusive pages of the citation
• Publisher – a text field containing the publisher name for edited volumes or books.
• PublisherLoc – a text field containing the publisher location for edited volumes or books.
• DocumentFormat – a text field specifying whether ABC has the document in electronic or paper form.

CropNames_Def

• CropID – a text field containing a four letter code for each crop.
• CropName – a text field containing the names for all crops grown in the U.S. and Canada.

LocAssoc

• LocAssocID – an autonumber field which serves as the unique identifier for the location associated with the AssociationID.
• AssociationID – a number field containing the AssociationID for this bird crop association. AssocationID is defined in the BirdCropAssoc table.
• StateID – a text field containing the name of the state (or Canadian Province) for this bird crop association. States are defined in the LocDef table.
• CountyID – a text field containing the name of the county (if any) for this bird crop association. Counties are defined in the LocDef table.
• LocDesc – a text field containing any other geographical location data about this bird crop association such as city or wildlife refuge.
• BCR_ID – the BCR number associated with the location for this bird crop association.

LocDef

• StateID – a text field which contains the two letter designations for all states and Canadian provinces.
• CountyID – a text field which contains the names of all counties in the U.S.
• FIPS – a text field containing the Federal Information Processing Standard (FIPS) code for all U.S. locations. These codes standardize identification of geographic locations within the U.S.
• Acres – a number field containing the acres associated with each FIPS code.
• Latitude – a number field containing the latitude associated with the FIPS code.
• Longitude – a number field containing the longitude associated with the FIPS code.

MonthAssoc

• MonthAssocID – an autonumber field which serves as the unique identifier for the months associated with an AssociationID.
• AssociationID - a number field containing the AssociationID for this bird crop association. AssocationID is defined in the BirdCropAssoc table.
• MonthID – a text field containing the name of the month for this association.

MonthDef

• MonthID – a text field containing the name of each month of the year.
• SeasonID – a text field which assigns each month to a season (Sep, Oct, Nov = fall, Dec, Jan, Feb = winter, Mar, Apr, May = spring, Jun, Jul, Aug = summer).

SpeciesRef

• CommonName – a text field which contains the common names of all bird species accepted by AOU.
• ScientificName – a text field which defines the scientific name for each common nam.
• Family – a text field which contains the family for each common name.
• Order – a text field which contains the order for each common name
• ESA_listed – a text field specifying yes if the species is listed under the endangered species act and no if it is not.
• ESA_status – a text field specifying if the species is threatened or endangered if it is listed on the ESA.
• ESA_year – a text field containing the year the species was listed on the ESA.
• Reference – a text field containing the reference which defines the common name.
• REF_year – a number field containing the year of the reference which defines the common name.
• GreenList – a text field specifying whether the bird is listed on ABC’s Green List or not.
• GreenList_Status – a text field which specifies, if the species is listed on the green list, which of three Greenlist categories the species falls into: (1) the highest continental concern, (2) moderately abundant species with declines or high threats, or (3) species with restricted distributions and low population size.
• PIF_Score – a number field containing the Partner’s in Flight (PIF) combined species assessment score for each species. These scores to objectively evaluate the conservation status of each bird species in North America.

StateDefs

• StateID - a text field which contains the two letter designations for all states and Canadian provinces.
• StateName – a text field which contains the full name of all states and Canadian provinces.

UsageDef

• Usage – a text field which defines the usages (brood rearing, cover, edge, foraging, incidental, loafing, nesting, roosting).
• UsageDesc – a text field which defines the usages.

Technical Information for Using the Offline BIAA Database

Queries – all queries are designed to support reports and should not be executed directly by the user.

• Bird Crop Associations by Bird – supports the report of the same name.
• Bird Crop Associations by Crop – supports the report of the same name.
• Bird Crop Associations by BCR – supports the report of the same name.
• Bird Crop Associations by State – supports the report of the same name.
• Citation Search by Citation ID – supports the report of the same name.
• qryBCR – returns a list of BCRs for which there is information in the database.
• qryBirdCropAssociationsAll – returns all bird crop associations.
• qryCitationRelationships – determines whether there is a citation relationship.
• qryCitations – returns the current list of citation IDs.
• qryCitationsAll – returns the current list of citations with citation information.
• qryConservationStatus – returns the conservation status of a bird.
• qryCrop – returns a list of crops for which there is information in the database.
• qrySpecies – returns a list of birds for which there is information in the database.
• qrySpeciesAll – returns a list of all birds defined by the AOU.
• qryState – returns a list of states/provinces for which there is information in the database.
• StateQuery – returns a list of all state/province names.

Forms – used to enter data. Citations must be added using the EnterCitations form before data can be added with the EnterBirdCropAssoc form. The CitationID obtained from the EnterCitations form is required to enter bird crop associations in the EnterBirdCropAssoc form. To enter a citation relationship, first enter both citations using the EnterCitations form and then establish

• CitationRelationships – used to enter citation relationships. Fields are primary citationID and secondary citationID.
• EnterCitations – used to enter citation data. Fields include citation ID (automatically generated), title, year, volume name, volume, number, pages, publisher, publisher location, document format (either paper or electronic). The Author/Editor subform allows you to input multiple authors or editors per citation. It includes an author/editor designation field, a primary author designation field, and last name and initials fields.
• EnterBirdCropAssoc – used to enter bird crop associations, required fields include common name, usage, crop name, citation id (obtained from the EnterCitations form), state or province, BCR, and month(s). Other fields include county and location description (for additional location information such as city or wildlife refuge).
• AuthorEditor Subform – used as a subform on the EnterCitations form for entering author and editor data. Should not be used directly.
• LocAssoc Subform – used as a subform on the EnterBirdCropAssoc form for entering location data (state, county, location description and BCR). Should not be used directly.
• MonthAssoc Subform – used as a subform on the EnterBirdCropAssoc form for entering month data. Should not be used directly.
• frmSelectBCR – presents a list of available BCRs to select from when running the Bird Crop Associations by BCR report.
• frmSelectCitation – presents a list of available citations to select from when running the Citation Search by Citation ID report.
• frmSelectCrop – presents a list of available crops to select from when running the Bird Crop Associations by Crop report.
• frmSelectSpecies – presents a list of available species to select from when running the Bird Crop Associations by Bird report.
• frmSelectSpeciesAll – presents a list of all species to select from when running the Conservation Status report.
• frmSelectStateProv – presents a list of available State/Provinces to select from when running the Bird Crop Associations by State report.
• SwitchBoard – presents the opening screen when starting the database which provides a selection between Entering a Citation, Entering a Bird Crop Association, Running a Report, and Managing the Database.
• ReportSwitchBoard – presents a selection screen of the different reports available.

Reports

• Bird Crop Associations by Bird – presents a list of bird names which have data in the database and returns crop associations, usages, seasons, comments, and citation ID.
• Bird Crop Associations by Crop – presents a list of crop names which have data in the database and returns common name, usages, seasons, citation ID.
• Bird Crop Associations by BCR – presents a list of BCRs which have data in the database and returns common name, crop, usages, seasons, and citation ID.
• Bird Crop Associations by State – presents a list of states and provinces which have data in the database and returns common name, crop, usages, seasons, and citation ID.
• Citation Search by CitationID – presents the list of citationIDs in the database and returns article title, year, volume name, volume, number, pages, document format, authors and which author is the primary.
• Conservation Status – presents a list of bird species defined by the AOU and returns the ESA status (endangered, threatened or none), ABC Green List status (yes or no), Partner's In Flight (PIF) combined species assessment score, and conservation priority (high, moderate, or low). The conservation priority ranking is based on the PIF scores.

Appendix B – Birds in Agricultural Areas Database Valid Crop Names

Appendix C – Birds in Agricultural Areas Database Valid Bird Conservation Region Numbers and Names

This database documentation was compiled by Sue Heath. It was last updated on August 20, 2004.