1: Keep the default ″import the source data into a new table in the current database.″ CSV Screen 2: Select ″Delimited″ Screen 3: First, select ″Comma″ as the delimiter. Second, select a double quotation mark (″) as the text qualifier. Third, click ″First row contains field names″. The order of the last two steps matters, and Access will sometimes reject ″field names″ if it doesn′t recognize data as text. Screen 4: Field options can usually be left as their defaults. Screen 5: Let Access create a primary key. Finish and close. Be prepared to troubleshoot! Check for any data that fails to import. You may have missed a step, or you may have to change the data type for particular columns. Table names Each imported CSV file appears as a table in the left-hand side of Access. By default, it uses the file name from the CSV, which is long and impractical. Rename the tables to something shorter. For example, ″growthCARBONOProjectClarkClark19972018″ could be renamed ″growth″. Note: The new table name should contain only lowercase letters, numbers, and underscores. Do not use spaces in your table name! Use underscores (″_″) instead of spaces. This will prevent problems later! (Edit, 15 September 2021: Emphasis added.) Relationships in Access To submit queries to the imported multiple tables, you will need to specify relationships between them. In the tables we are using, ″plottree_id″ is a unique identifier for each individual tree used in the tables we imported. From the ″Database tools″ tab, select ″Relationships.″ It will open a new tab, where you can specify connections between tables. Use ″Add tables″ on the right to add a box for the tables in the main workspace. Each box shows the columns in the table. There are a few ways to specify connections between tables. You can drag and drop column titles between boxes to open a dialogue. When you have a ″Design″ tab visible, you can click the ″Edit relationships″ button to bring up a dialogue. Specify relationships between the ″plottree_id″ column in each table for those that have it. Specify relationships between the two tables that have ″genspcode″, IndividualsCARBONOProjectClarkClark19972018.csv″ and ″SpeciesCARBONOProjectClarkClark19972018.csv″. (Edit, 15 September 2021: Added the text about ″genspcode″.) Queries From the ″Create″ tab, press the ″Query wizard″ button. When the dialogue opens, select ″Simple Query Wizard″. You can then specify multiple tables and fields you want to include from each. Use this tool to create a table that shows the tree ID, species, growth, and year. Edit, 15 September 2021: To be more specific about your query: Add ″plot_treeid″ from the ″individuals″ table. Add ″genus″ and ″species″ from the ″species″ table. Add ″diagro″ and ″growth_year1″ from the ″growth″ table. When asked ″detail or summary query?″, select ″Detail″. Then select ″Open the query to view information.″ Once you do this, it will create a ″Query″ section in the ″All Access Objects″ menu on the left, which usually has the table name, ″Query″ and a number. Edit, 5 September 2021: When you see the query results (a standard looking spreadsheet), you should see: Five columns A total number of records in the lower left corner. If all goes well, it should read, ″1 of 92682″. Graph Microsoft Access has no graphing capabilities, so to create a plot, you need to export it to some other program. The ″External data″ tab gives you export options, including Excel and text. Export to text Excel. (Edit, 15 September 2021: Text export is causing problems for unknown reasons.) The Export Wizard will open. If exporting as text, make sure to select comma as the delimiter and select ″Include field names in first row″. Within Excel, use the skills you demonstrated in the Week 01 Assignment to make: A graph showing the raw number of trees with negative growth (i.e., negative values in ″diagro″) in each year (from 1997-2017). (Edit, 15 September 2021: Clarification.) A pivot table that lists of the four species (with full species names) that showed the most individuals with negative growth across all years. Submit one Excel file with separate sheets for the two parts. From the ″Create″ tab, select ″Query design″. A ″Design″ tab will open in the top ribbon, and a ″Query″ tab will open below. In the ″Results″ section of the ribbon, there is a ″View″ option that toggles between ″Design view″ and ″SQL View″. Select ″SQL view.″ (You can also toggle between these views by right clicking the query tab at the top of the main workspace, above where you will type your query. Right clicking the tab opens a dropdown menu with ″Design view″ and ″SQL View″ as two options.) The main workspace now provides a starting prompt that reads ″SELECT;″. ″SELECT″ is usually the first word in a SQL query and a semicolon indicates the end of an instruction. Note: Remember how I said last week that SQL was not a true standard and that it could vary? In the SQLzoo website, queries didn′t have to end in any particular way. In Access, every query has to end with a semicolon. There are a few other little differences you might encounter, too. 29 September 2021: If you use the Access file provided below, the ″ID″ column is added by Access. It is an arbitrary number assigned rows in a table. So the ″ID″ column should not be used as a key on a ″JOIN″ command, because the numbers in different tables are not ″the same″ in any meaningful sense. Write SQL code for queries that extract the following data from your database. (Not all of these require the ″join″ operator.) Negative growth query: The year and species (: Specifically, the ″genspcode″ variable) for all trees that showed negative growth in a given year. (I asked for these data previously using the built-in ″Query Wizard″ in Access. You can use last week′s dataset to make sure that you are getting the same results by these two methods.) Biomass query: For trees that grew in a given year (the ″POMchange?″ in the original file) for 2005 onward, the year they grew compared to previous year, and their estimated biomass for the year. (: To put it another way, ″From 2005 on, find the trees that grew, and tell me how big they were. That is, their biomass.″) (: The result should include the tree, the year, and the biomass.) Death query: For specific (one might even say ″DISTINCT″) trees that died during the study, the year the tree died and the type of soil it was growing on (the ″edaphic″ variable in the original files). (Clarified that query should not return duplicates.) Multiple stems query: For each DISTINCT tree with multiple stems, the stem ID, the genus and species code (the ″genspcode″ variable), the full genus, name. and the type of soil it was growing on (the ″edaphic_code″ variable).(: Clarified soil column variable.) (: Clarified that query should not return duplicates.) These answers will be graded by a person, not automatically, because they are more complex queries and probably have more valid ways of being written. So there are no formatting restrictions this time. But because these will be graded by a person, please do everything you can to make each query easy to read! I have included a Word template to get you started (″Week_04_SQL_template.docx″). Remember: There is no quick way to remember all SQL terms. If you get stuck, resources like this are your friend: From the ″Create″ tab, select ″Query design″. A ″Design″ tab will open in the top ribbon, and a ″Query″ tab will open below. In the ″Results″ section of the ribbon, there is a ″View″ option that toggles between ″Design view″ and ″SQL View″. Select ″SQL view.″ (You can also toggle between these views by right clicking the query tab at the top of the main workspace, above where you will type your query. Right clicking the tab opens a dropdown menu with ″Design view″ and ″SQL View″ as two options.) The main workspace now provides a starting prompt that reads ″SELECT;″. ″SELECT″ is usually the first word in a SQL query and a semicolon indicates the end of an instruction. Note: Remember how I said last week that SQL was not a true standard and that it could vary? In the SQLzoo website, queries didn′t have to end in any particular way. In Access, every query has to end with a semicolon. There are a few other little differences you might encounter, too. 29 September 2021: If you use the Access file provided below, the ″ID″ column is added by Access. It is an arbitrary number assigned rows in a table. So the ″ID″ column should not be used as a key on a ″JOIN″ command, because the numbers in different tables are not ″the same″ in any meaningful sense. Write SQL code for queries that extract the following data from your database. (Not all of these require the ″join″ operator.) Negative growth query: The year and species (: Specifically, the ″genspcode″ variable) for all trees that showed negative growth in a given year. (I asked for these data previously using the built-in ″Query Wizard″ in Access. You can use last week′s dataset to make sure that you are getting the same results by these two methods.) Biomass query: For trees that grew in a given year (the ″POMchange?″ in the original file) for 2005 onward, the year they grew compared to previous year, and their estimated biomass for the year. (: To put it another way, ″From 2005 on, find the trees that grew, and tell me how big they were. That is, their biomass.″) (: The result should include the tree, the year, and the biomass.) Death query: For specific (one might even say ″DISTINCT″) trees that died during the study, the year the tree died and the type of soil it was growing on (the ″edaphic″ variable in the original files). (Clarified that query should not return duplicates.) Multiple stems query: For each DISTINCT tree with multiple stems, the stem ID, the genus and species code (the ″genspcode″ variable), the full genus, name. and the type of soil it was growing on (the ″edaphic_code″ variable).(: Clarified soil column variable.) (: Clarified that query should not return duplicates.)
