Designing Databases for Biological Research - Study Guide | NR 385, Exams of Earth Sciences

Download Designing Databases for Biological Research - Study Guide | NR 385 and more Exams Earth Sciences in PDF only on Docsity! Designing Databases for Biological Research NR 385 University of Vermont Rubenstein School of Environment and Natural Resources Class 2, Part 3: September 20, 2008 1. Create the Location form (9:30 – 9:45) a. Create a form i. Access 2007 is able to automatically create forms that are often quite close to what is needed. ii. Click once on tbl_Locations, and then check out the Create… Forms sub-menu. iii. There are a variety of layouts available. 1. Form: Presents information with the data for a single record next to the field names; will automatically add in a sub-table that is linked to the selected table. 2. Split Form: Presents information with the data for a single record next to the field names at the top of the form, and several rows in a datasheet at the bottom of the form. 3. Multiple Items: Presents information with data for multiple records listed beneath the field names. 4. Datasheet: Similar to “Multiple Items”, with fewer formatting options (basically a spreadsheet). 5. PivotTable: A specialized format that is potentially useful for displaying data summaries. 6. PivotChart: A specialized format that is potentially useful for displaying data summaries. iv. Try out a couple of layouts to get a feel for what they look like. v. The “Form” layout is probably the best for our purposes, since we want to display visit and observation data for each site on the same form. vi. After generating a form using the Form layout, save it as frm_Locations. b. Examine the new form i. Does the order of the fields make sense? ii. Tab through the fields. Does this tab order work for you? iii. The percentages have a lot of decimal places, and we may want to modify that. iv. We can also change how the data is justified. c. Click into form design view. The Design menu should be activated, which has a large “Controls” section, and you should also see the Property Sheet to the right side of the form. d. There are three main sections of the design screen i. Form Header: Information that will always appear at the top of the form, and that does not contain record-specific information. ii. Detail: The “meat” of the form, the detail area contains your data records. iii. Form Footer: Information that will always appear at the bottom of the form, and that does not contain record-specific information. e. To create a header or footer when one is not visible, hover the cursor just under the header or footer label, until you see a double-headed arrow. Then click and drag down to create some design space. f. Properties Dialog i. Click in different sections of the form, and notice how the Properties box changes. The contents of this box are context-specific, and vary depending on what type of form object you are working with. ii. Let’s take a tour of some useful properties. 1. Disable or Lock or Hide a field Page 1 a. Sometimes you will have controls on your form that display data only, and that you do not want changed. Sometimes you need to have a control present, but not visible to the user. Which option(s) are appropriate here? b. Disabling a control allows it to be seen, but not clicked in or altered by the user. i. Click on the Data tab, and set “Enabled” to “No” to disable a control. c. Locking a control allows a user to click into the field, but not change the data. i. Click on the Data tab, and set “Locked” to “Yes” to lock a control. d. Hiding a field prevents a user from even seeing the information, although calculated controls can use the data. i. Click on the Format tab, and set “Visible” to “No” to hide a control. e. You can also delete a field, if you do not need it. i. Right-click on the field itself, and select “Cut”. 2. Selecting multiple controls a. You can select multiple controls by clicking and dragging a selection box over them. All of the selected controls will be highlighted with dots at the corners and midpoints. b. Select the three controls that display habitat information. 3. Decimal Places a. Change the number of decimal places by selecting the Fixed number format in the Format row of the Format tab b. Select the number of decimal places using the Decimal Places row. 4. Justification a. Highlight all of the controls, and choose a text alignment from the Font section of the Design menu, or from the Text Align property on the Format tab. 5. Field Width a. You can adjust the control width by clicking and dragging on the dots surrounding a selected field, or by using the Width and Height rows of the Format tab. g. Control order and tab order i. To change the order of your controls, highlight the controls you want to move and drag them to a new location. 1. Move the habitat fields below the coordinates. ii. Go to form view, and test out your tab order. You’ll probably find that the “focus” moves to the visit table after the coordinates. iii. Go back to design view, and right-click in the small box beneath the form name (upper left corner of the form), and select “Tab Order”. You can rearrange the order here by clicking and dragging to the left of the control names. iv. You can remove a control from the tab list, or alter the tab order, by selecting the control and using the Tab Stop (whether or not the control can be entered by tabbing) and Tab Index (the order with which controls are cycled through) properties on the Other tab of the Properties dialog. h. Add a calculated control i. It might be useful to add a calculated control that totals up the percentages of the different habitat types. ii. Make the form big enough for a new control. iii. Method 1: 1. Click on the text box icon in the Toolbox (“AB|”) 2. Click on the form where you want the new field to appear. Access places the new text box, and gives it a default name. Page 2 c. It helps to set the database to automatically compact when it is closed: Office Button… Access Options… Current Database… Application Options… Compact on Close. 7. Verify constraints on data entry and refine the forms (10:30 – 10:40) a. Open the Location form, and create a new location (click on the right arrow and asterisk icon at the bottom of the form) and enter some data. Verify that the constraints you have set are functioning. b. Look for places where data entry can be simplified, particularly with lookups instead of text boxes, and with input masks. c. Create input masks for Visit_Time and Visit_Date on the Visits subform. i. In design mode, click in the Visit_Time field, and on the Property Sheet go to the Data tab and click on the ellipses next to Input Mask. ii. This starts a wizard; select Short Time, and Finish. iii. Do the same for Visit_Date, selecting the Short Date option. iv. However, since all the observations were from 2004, it would be nice not to have to type the year. 1. Modify the input mask from: 99/99/0000;;_ to 99/99”/2004”;0;_ 2. The 0 between the semicolons tells Access to store what is literally on the screen; putting items between quotes tells access that the items are part of the mask (characters to be displayed), not input mask codes. d. It would really help to have a lookup for bird codes that auto-completes as you enter observations. i. In design view, right-click on Species_ID and select “Change to… Combo Box”. ii. In the Data tab of the Properties dialog, make sure “Table/Query” is the row source type, and set “tlu_Species” as the row source. iii. Make sure “Limit to List” is set to “Yes”. iv. What would you need to do to have the database display the common name and store the species code? 1. On the Format tab of the Properties dialog, change the Column Count to 2, and set appropriate Column Widths; separated by semicolons. 0.6 and 2.0 are about right. 2. Set the List Width to the summed column widths, instead of “Auto”. e. It would also help to display “Auditory” and “Visual” rather than “A” and “V” for detection types i. Change to Combo Box, set the Limit to List property. ii. In the Data tab of the Properties dialog, make sure “Value List” is the row source type, and set “A;Auditory;V;Visual” as the row source. iii. Change the Column Count to 2, and set the first column to 0”, and the second to 0.7”; set the List Width to 0.7. 8. Delete Test Data a. Do you remember how to set up cascading deletes? i. Database Tools… Relationships ii. Right-Click on each relationship, and select Edit Relationship… Cascade Delete. iii. Open tbl_Locations, and right-click to the left of the temporary location you entered. iv. Select Delete Record, and say Yes to the warning. v. Now go back to the Relationships window and uncheck Cascade Delete in each relationship. 9. About Queries (10:40 – 10:45) a. In general, you can edit data displayed in a select query, unless that data is summarized (Totals). b. Joins i. We’ve already seen some queries where we’ve had multiple tables used in the query. These tables were linked, or joined, automatically because we have already defined relationships between the tables. ii. Inner Join: The default join type is an inner join; you only see records from the joined table that match. 1. This means that “orphan” data that does not match between tables is not displayed. Page 5 iii. Outer Join: This type of join shows ALL records from one table, and only the records from the linked table that match. 1. This type of join is a good way to find orphaned data. 2. Another use is for displaying rows of information where the database has no data. For example, if we had a more complete tbl_Species (i.e., including species that were not detected), we could display a complete Vermont bird list, and how many birds of each species were detected in our study. 10. Select Queries (10:45 – 11:30) a. You’ve already had an opportunity to create several select queries. These queries are the most common type, and basically present database records or summaries of records. Select queries can also be used to perform operations on data, including calculations and conditional expressions. b. First we’ll do some prep work so we can see how joins work i. Import the spreadsheet Bird_Codes_VT into a new table, tlu_Bird_Codes_VT. 1. External Data… Import… Excel 2. Make sure to check “First row contains headings” and choose the bird code as the primary key. ii. Open frm_Locations and add a site 400.A, but do not add a visit or observations. iii. Click the >* button at the bottom of the form, and immediately start entering observations without assigning a Location or Visit. Enter 2 observations. Close frm_Locations c. Look for orphaned data i. Look for Locations with no visits 1. Create a select query with tbl_Locations and tbl_Visits. 2. Add the primary key from both tables. 3. Change the join type so that all records from tbl_Location are shown. Notice that the relationship line now has an arrow. 4. View the results; you may need to sort the records to bring the null field to the top of the screen; for site 400.A there is no corresponding Visit record. 5. What would happen if you changed the join type to show all records in tbl_Visit? a. With an inner join, or an outer join on tbl_Visit, site 400.A does not display. 6. Save the query as qry_Locations_and_Visits ii. Look for Observations with no visits 1. Create a select query with tbl_Observations and tbl_Visit. 2. Add the primary key from both tables. 3. Change the join type so that all records from tbl_Observations are shown. 4. View the results; you may need to sort the records to bring the null field to the top of the screen; you should find some observations where there is no corresponding Visit record. 5. What would happen if you changed the join type to show all records in tbl_Visit? a. With an inner join, or an outer join on tbl_Visit, the orphaned records do not display. 6. If you set the criteria for the visit field to “Is Null”, the query will only display orphaned observations. 7. Save the query as qry_Orphaned_Observations 8. Go ahead and delete the new location and the orphaned data. d. Determine how many individuals were detected at each site i. What tables do you need? tbl_Location, tbl_Visit, tbl_Observations ii. What fields do you need? Location_ID and Observation_ID iii. What else do you need to do? You want summary information, so click “Totals” (the Sigma icon). Click the drop-down under Observation_ID where it says “Group by” and select “Count”. This will then display the number of observations for each site. Note that there are many other summary options available in the “Total” row of the query. Page 6 iv. We are concerned that we may have counted the same individuals multiple times (during different counts). What is the maximum number of individuals detected during a single count at each site? This is probably a safer estimate of the bird population at each site. v. What do we need to change on the query? vi. First, we need to add the visit (Visit_ID) to the query. Now our count shows the number of individuals detected during each visit. How do we pull out just the maximum value? vii. Unfortunately, you can’t add a second layer of totals to a query. But you can create a new query based on this one. Save this query as qry_Individuals_Per_Visit. viii. Start a new query, based on qry_Individuals_Per_Visit ix. Add Location_ID and the CountOfObservation_ID field to the query, select Totals, and under Group By for the Count field, select Max. Verify that this query does what you wanted by comparing the results to qry_Individuals_Per_Visit. Save the new query as qry_Individuals_Per_Site_Max x. It is possible to write long chains of queries to accomplish successive total and summarizing tasks. e. Determine how many species were detected at each site i. Try this one on your own! This will also require two queries: one to create a bird species list for each site, then a second to summarize the number of species at each site. ii. What tables do you need for the first query? tbl_Locations, tbl_Visits, tbl_Observations iii. What fields do you need? Location_ID, Species_IDF iv. Then what? Turn on Totals, Group By for both fields. v. Next, save the query as qry_Species_by_Site and create a new one based on it. vi. Add both fields to the new query, and select totals, and use Count for Species_ID, and save as qry_Num_Species_by_Site. f. Calculate descriptive statistics for habitat variables i. What are the average, min, max, and standard deviation of percent forest at the study locations? ii. You should be able to do this on your own. iii. What tables do you need? Only tbl_Locations. iv. What fields do you need? Only Forest_Percent, added multiple times. Note that in this case we want to summarize across all locations, so you do not want to add Location_ID, since this will cause a row to be created for each location. v. Choose Totals, and select the appropriate summary statistics for the query from the Total row. vi. Save as qry_Forest_Stats g. Now calculate the same descriptive statistics for sites where X_Coord is > 475000 i. One approach is to use two queries for this; the first query will select the appropriate sites using a statement in the criteria row of the query design grid (qry_Locations_East), and the second will repeat qry_Forest_Stats, based on qry_Locations_East. Call this query qry_Forest_Stats_East. ii. Another approach would use only one query. In the first column, type “East: iif(X_Coord > 475000, Yes, No)”, and set the criteria to “Yes”. Then turn on totals, and add Forest_Percent to the query grid several times, and select the appropriate descriptive statistics from the Total row. The “East” column would stay set to “Group By”. Save the query as qry_Forest_Stats_East. h. How different are the results of the two Forest Stats queries? Create a new query that displays the difference in percent forest between the two queries. i. Add both queries to the design matrix of a new query, and create a field that calculates the difference: [qry_Forest_Stats_East].[AvgOfForest]-[qry_Forest_Stats].[AvgOfForest] ii. Note that in this case, the queries don’t need to be linked since there is only one row of information in each. Page 7 a. Crosstab queries allow you to calculate and restructure data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information – one down the left side of the datasheet and another across the top. b. We want to generate an index of abundance for each species, based on the number of counts they were heard on. Then we want to see if there is a tendency for some species to be found or not found in habitat that is predominantly forested. The basis for this index is a query that lists the location, percent forest, and counts that each species was found on. i. Create this query, based on tbl_Locations, tbl_Visits, and tbl_Observations. ii. The fields you need are: Location_ID, Forest_Percent, Species_IDF, and Count. iii. Turn on Totals, and save this query as qry_Species_by_Location_and_Forest c. Use qry_Species_by_Location_and_Forest to generate a crosstab query using the crosstab query wizard. i. Use the Query Wizard (Create… Other… Query Wizard), and select “Crosstab Query Wizard” ii. Select qry_Species_by_Location_and_Forest from the list of queries, and click Next. iii. Select Forest_Percent and Location_ID as row headings, and click Next. iv. Select Species_IDF as the column heading, and click Next. v. Select Count under “Fields” and Count under “Function,” and click Next. vi. Name the query xtab_Species_by_Location_and_Forest, and click Finish. d. View the results and see if you can find any species that seem to be more common in forest habitat, and any species that don’t seem to be common in forest habitat. e. It may help to “Freeze” the first two columns of the query. Highlight both columns, then click Format… Freeze Columns. 18. Union Queries (12:15 – 12:25) a. Union queries will combine information from multiple sources into a unified format. b. Union queries are useful for combining similar information from several tables. For example, in wildlife studies it is common to locate animals in a variety of ways: telemetry, capture, visual sighting, and mortality. Each of these types of locations has similar data: animal ID, coordinates, date, time, technician; and each also has unique information, such as error polygon size, condition on capture, distance from observer, and cause of death. A Union query can be used to pull together the information that is common to each table, and the query can also include a new field that references the source of the data. c. To practice making a union query, we will import some additional bird data (from a different observer), and combine the new data with our existing data. d. Download and import the file Bird_Data_2.xls into tbl_Raw_Data_2 (the file is on the web site as “Additional Bird Data”. e. We want our union query to have the following fields: Location_ID, Visit_Date, Visit_Time, Wind, Sky, Species_IDF, and Observer. f. Note that these fields may have different names in the two sources, but they have the same data types; if the data types differ then you will need to convert the data types so that they match (either “on-the-fly” within the query, or on the source data table(s)). g. Create a select query based on tbl_Observations, tbl_Visit, and tbl_Location that contains the desired fields. To create the Observer field go to an empty column and type: Observer: “Wilson” h. Now create a select query based on tbl_Raw_Data_2, using the matching fields and renaming the field names as needed to match the first query. i. Type the name of the field in the first query, followed by a colon, followed by the name of the field in tbl_Raw_Data_2. i. Click View… SQL from the second query, and copy the SQL code. j. Go to the first query, click View… SQL, and paste the SQL code below the code for the first query. k. Type “UNION ALL ” in front of the word “SELECT” in the segment you just pasted. l. Switch to datasheet view to see the results. Page 10 m. Save the query as qry_Combined_Data. n. Note that to combine additional information into this query, you would simply build a new select query that uses the same field names and data types, copy the SQL code and paste it below the existing code, and add “UNION ALL ” before the new “SELECT” statement. 19. Pass-through Queries (12:25 – 12:30) a. A Pass-through query allows you to execute an SQL statement directly against the tables in an external database. It is unlikely that you’ll need to use pass-through queries… but just in case, a good tutorial can be found at http://www.techonthenet.com/access/tutorials/passthrough/basics01.php. 20. Data Definition Queries a. A Data Definition query is used to create, delete, or alter (add or remove fields or constraints) tables, or to create an index for one or more fields. This type of query should not be needed unless you are trying to automate complex data management functions using macros or Visual Basic. 21. Useful functions for queries (12:30 – 12:45) a. There are a number of Visual Basic functions that are helpful and available for use in queries. b. To get to the Visual Basic function reference, create a blank module and open help from the Visual Basic editor that appears. c. In the help window, go to Contents… Visual Basic Language Reference… Functions. d. You can also search for specific functions from the Visual Basic help window. e. Some useful functions: i. Mathematical 1. Abs(number) = returns the absolute value of “number” 2. Int(number) = returns the integer portion of “number”; does not round 3. Round(expression, decimals) = rounds “expression” to “decimals” decimal places a. Create a query based on tbl_Location that creates new fields for percent forest, using the Int and Round functions ii. Text 1. & = concatenates two strings of text 2. Left(string, length) = returns the left-most “length” characters of “string” 3. Mid(string, start, length) = returns the first “length” characters of “string”, starting at character number “start” 4. Right(string, length) = returns the right-most “length” characters of “string” a. Extract portions of the observer’s name in tbl_Raw_Data or tbl_Raw_Data_2, and combine them with the Site_Station field. iii. Conversion 1. CBool(expression) = converts “expression” to true or false 2. CByte(expression) = converts “expression” to number of length byte 3. CDate(expression) = converts “expression” to a date field 4. CDbl(expression) = converts “expression” to number of type double precision 5. CDec(expression) = converts “expression” to number of type decimal 6. CInt(expression) = converts “expression” to number of type integer 7. CLng(expression) = converts “expression” to number of type long integer 8. CSng(expression) = converts “expression” to number of type single precision 9. CStr(expression) = converts “expression” to text a. Conversion functions are especially useful during data import and export iv. Other 1. CurrentUser() = returns the current user of the database 2. Date() = returns the system date 3. Now() = returns the system date and time 4. Time() = returns the system time Page 11 5. Format(expression, “format”) = format the expression using a named format, e.g., “short date” 6. Iif(expression, truepart, falsepart) = conditional statement, returns truepart if expression is true, falsepart if expression is false f. Add Date, Now, and Time fields to an existing query. g. Use the Format function to alter how the dates and times are displayed (note that you can nest functions). For example, in the query grid, use the following as a field name: Current_Date: format(date(), “Long Date”) h. CurrentUser, Date, Now, and Time can be useful for keeping track of who makes changes and when changes were most recently made to a table, by using an Update Query via a macro whenever data is changed. Page 12