You have been given a workbook that contains several years of fish count data from the Great Smoky Mountains National Park.  Using your best judgement, please complete the tasks below, keeping in mind that for the database design, there is no one “right” answer.  You may wish to refer to your K201 or K204 information if you need to refresh yourself on queries.

Please note: The data that you have been provided is actual data that has been collected.  As such, there may be irregularities, duplications, and decisions made that did not make for clean data collection.  YOU must make decisions about how to use this data: should you include everything? Should you include just the pieces that are requested? Should you rename primary keys, or add additional information so that there is not duplicate data or any ambiguity in the fields?  This is a complex assignment, not only because you have to make a database and an Excel form, but you also need to rationalize how you will use the data given to you to complete these tasks.

Access Component

1.    Examine the GRSM_Fisheries.xlsx workbook, making special note of data that is duplicated on different worksheets

2.    Using this data, create a database in Access. At a minimum, you must include all of the data and fields from the “Fish Species” worksheet.  Specifically, your database must have:

1.    At least three tables. Aside from primary keys, there should be no fields that appear in more than one table.  All fields from the “Fish Species” worksheet must be included (at a minimum), along with any other fields you decide to add

2.    At least two relationships formed between the tables, such that any changes will automatically propagate throughout the connected tables.

3.    You may make any changes to the column headers that you feel are important. For this database, every record in every table should have a unique piece of data to use as a primary key for the table.

Excel Component

Create a form to aid in data collection for future entries.  This form should have the following attributes:

Basic Design:

1.    Place a button labeled New Entry in G1 on the Fish Species worksheet. When depressed, this button will launch the userform.

2.    Text explaining the purpose of the userform, and a label for each object. Buttons should be named as indicated, but do not need an additional label.

3.    A background color for the userform that is any color besides gray

4.    At least one image on the userform

5.    A text box that will capture the person entering the data's username, and display it by default in a text box with the label Observer

6.    A text box that will display the current date, in DD-MMM-YYYY format (medium date format), where MMM is the three-letter code for the month (Jan, Feb, Mar, etc.)

Advanced Design:

9.    A drop-down box that will display either the state of North Carolina or the state of Tennessee when activated by the user

10. A list box that will display each of the counties for the state chosen from the drop-down box. Users should only be able to select one county in the list box at a time.

11. A list box that will display each of the watershed areas for the county selected. Users should only be able to select one watershed area at a time

12. A final list box that will display all the available streams for the watershed area selected. Again, users should only be able to select on stream at a time

13. A drop-down box that will display all 43 types of fish that could be observed, in alphabetical order

14. A text box where the user can input the number of fish counted of the type selected in step 10. The default value for this text box should be the number one.  Each type of fish observed will require an addition entry by the user.

Buttons:

Please note: if a button does not have the correct label, you will not receive points for the button.

Record Observation This button will create a new entry on the Fish Species worksheet, based on the selections made on the form. For the column labeled "observer," please insert the username of the person filling out the form.  Every column on the Fish Species worksheet must be completely filled out, without adding additional objects to the form. Once this button has been pressed, all form objects should return to their original state.

Record Observation and Record Additional Species This button will allow the user to enter a new species and number counted, WITHOUT having to enter the user, date, state, county, watershed, or stream name.  These values should persist from the first observation entry, if one had been made.  The only items that will be reset by this button are the species list and the text box for number of fish to be entered (please remember: the default value for this box should be one).

Reset Form All form objects should return to their original state, as if the user had opened the form again.  The only selections that should be made are the default values for the text boxes, as indicated previously.

Cancel Closes the form without recording any data to the worksheet