As you know, Northern Nevada Green Coalition is interested in showing how green energy production can help to grow and diversify Nevada's economy. In order to do that, we need to analyze our data about the potential for green energy production in Nevada. We will use this analysis for a future presentation to officials from industry and local and state governments. We need to summarize the data from the spreadsheet you created in the last project in a way that highlights the alternative energy potential in Nevada. We also need to move this information to a database that will provide more flexibility in analyzing and reporting the information.
Task I. Summarize Spreadsheet Data
Add a second sheet to the Excel workbook you created in the last project. Be sure to give the tabs for each sheet a meaningful name. The second sheet will be a summary of the data on the first sheet. We will be modifying the spreadsheet as technology and market conditions change, so it is important to use the features described in the Project Evaluation, especially the cell referencing feature. You should put the following three items on this second sheet:
- Reference the data on the first sheet to produce a table similar to the following:
- A Bar Chart illustrating the data in the table above.
- A Pie Chart showing the three types of energy and their percentage of Nevada's total energy potential.
This information will be used for a professional presentation, so be sure to include titles and legends so people will understand what they are viewing.
Task II. Import the Data to and Access Database
I'm continuing to gather data and it is becoming clear that a spreadsheet will not be adequate to organize it. I need you to use your skills to get this into a database that will allow us to organize our information and extract specific things we need. I have provided a link to the file named Access Data that contains the original data you used for your spreadsheet, plus some new data I have begun gathering. Please import each of these sheets into a separate table in one new Access database. Name the tables Energy Data and Contact Information. Once the data is correctly imported, you should complete the following:
- Check the database design to insure the fields in both tables are defined as shown below.
o State Name - Text, size 20, primary key
o Geothermal - Long integer, zero decimal places
o Solar - Long integer, zero decimal places
o Wind - Long integer, zero decimal places
o Website - Hyperlink
o Email - Text, size 40
o Phone - Text, size 12
- The Contact Information data is not complete, so you should create a form using the Forms Wizard to allow us to finish entering the data in this table. Use a justified layout and apply a style other than the default.
- I also need the queries described below to include in our presentations. Queries that list multiple states should be sorted from largest energy potential to smallest.
o A query named Geothermal that lists the state name and geothermal energy potential for only states that have geothermal energy (greater than 0). You should have 13 records and 2 columns.
o A query named Solar that lists the state name and solar energy potential for all states that have at least 58,000 MWe of solar potential. You should have 15 records and 2 columns.
o A query named Solar Average that lists the minimum, maximum and average for solar energy potential. You should have 1 record and 3 columns.
o A query named GeoSolar that lists the state name, geothermal energy potential and solar energy potential for all states that have at least 50,000 MWe of solar potential and more than zero geothermal potential. Include a calculated field showing the total of solar and geothermal energy for each state. You should have 8 records and 4 columns.
o A query named GeoContact that lists the state name, website, email and phone for all states that have more than zero geothermal energy potential. (You will need to join the tables on the State Name field to do this.) You should have 13 records and 4 columns.
I look forward to seeing these results so we can use them in our presentation.