Databases Assignment 3

1. Create a copy of the Portal Database by either copying a version you already made or following the instructions in the Joins 1 exercise and create a basic data entry form for the species table. Since we would only use this form to add new species to the table, do not include the OldCode field in the form. Also, New Code isn’t very clear for someone entering data, so change the label on this field to Species Code. Save this form as Species Table Data Entry.

2. Create a basic data entry form for the Portal surveys table. Since the recordID field will automatically add a new number, don’t include it in the form. Save this form as Surveys Table Data Entry.

   Use this form to enter the new data for the following individual:

   Month: 11
   Day: 11
   Year: 2011
   Period: 1111
   Plot: 1
   Stake: 11
   Species: DM
   Sex: F
   Age: J
   Hind Foot Length: 32
   Weight: 40
   Tag: A112602
   

3. This is a follow up to the Forms 2 exercise

   Modify your basic data entry form to prevent bad data values from being entered into your database. You can do this using the Data tab on the Property Sheet in the Design View. The following rules should apply:

   1. Months should be numbers from 1-12
   2. Days should be numbers from 1-31
   3. Years should be numbers from 2014-2020 (older data is possible, but this form is form entering new data)
   4. Plots should be numbers from 1-24
   5. Stakes should be numbers from 11 to 77
   6. Species should be selected from a drop down list (called a combo box in Access) that includes only the species IDs that are present in the new_code field of the Species table. If the Species table is updated the new values should automatically appear in the combo box.
   7. Sex should be M or F or nothing
   8. Age should be J or nothing
   9. Hind Foot Length should be a number between 1 and 70
   10. Weight should be a number between 1 and 350

4. Create a report that asks the user what year they want the report for and then returns the number of individuals of each rodent species captured in each month of that year (grouped by month so that the report shows the counts for January, then the counts for February, etc.). This is the kind of report that might be provided to a government agency as a condition of conducting the research.

   Some specifics:

   1. The report should have a title that describes what is being reported and includes the year of the report
   2. Months should be reported using their names, not the numbers 1-12, and should be sorted chronologically. You will need to add a Months table to the database to accomplish this.
   3. Scientific names should be reported, not species iDs
   4. All titles/labels should be easy to understand for someone unfamiliar with the database, so make sure to change the defaults if they are unclear.

   Save this report as Monthly Trapping Report.