timer Asked: Apr 12th, 2020

Question Description

following the instructions and not difficult work.

Thank you!

following the instructions and not difficult work.

Thank you!

following the instructions and not difficult work.

Thank you!

following the instructions and not difficult work.

Thank you!

following the instructions and not difficult work.

Thank you!

Unformatted Attachment Preview

Quarter 1Q-2017 2Q-2017 3Q-2017 4Q-2017 1Q-2018 2Q-2018 3Q-2018 4Q-2018 Sales $ 200.00 $ 400.00 $ 350.00 $ 425.00 $ 450.00 $ 450.00 $ 475.00 $ 500.00 $600.00 $500.00 $400.00 $300.00 $200.00 $100.00 $1Q-2017 2Q-2017 Sales 2Q-2017 3Q-2017 4Q-2017 1Q-2018 2Q-2018 3Q-2018 4Q-2018 Lab Exercise 9: Integration of Access and Excel As we have learned in previous exercises, Excel and Access each have their own strengths and ways to use: • Access – Relational database that can be used to manage and maintain data in multiple data tables with appropriate relationships set up between the tables. Capabilities include forms to enter data, use of queries as a mechanism for locating all records that match a user-defined set of criteria, and reports to create structured output from the database. • Excel – Spreadsheet software that allows us to easily manipulate data including simple mathematical operations, more complex functions, and pivot tables. It also provides graphing and charting capabilities that are not possible with Access. To effectively use the data in our databases, it would be extremely useful to be able to utilize the strengths of both. The purpose of this lab is to show some of the ways the usage of Access and Excel can be integrated. Let’s examine some of the ways this integration could be useful working with the database that was being used in the Access exercises. • Including all zip code/city name pairs in Cook county into the Access zipCode table from a list that is available in an Excel spreadsheet from some other source • Using a query to find what quantities of each item has been purchased by each customer. • Exporting the results of that query to Excel for further analysis • Being able to graph various combinations of that data. There are three starting files for this lab exercise that should be downloaded from your lab section blackboard site and stored on your documents folder (or elsewhere if you prefer – just remember where): • Lab9.Simple.Graphing.xls – This Excel file will be used to demonstrate the simple graphing capabilities of Excel. • Lab9.database.accdb – this database is similar to the database used in Lab Exercise 8. • Lab9.cook.county.zipcodes.xls – this Excel worksheet contains a list of the zip codes and cities in Cook County that was found on the internet Part 1 – Simple Graphing in Excel There are some relatively straightforward graphing/charting capabilities within Excel. The first part of this exercise will demonstrate some of those capabilities. Visual representation of data may often show information that might not be otherwise be recognized. Excel has many different kinds of charts – bar charts, pie charts, etc. We are going to demonstrate a basic line chart based on some simple data you might be analyzing. In the starting Excel file for this part of the exercise, there is a worksheet that contains quarterly sales of an item for 2017 and 2018. We might be interested in seeing if there is some pattern in this data and a line chart may make this much more obvious than just looking at the numbers. Action 1 – Open the starting file – Lab9.Simple.Graphing – In Excel You should see a worksheet with a column for the quarters and another for the sales. 1 Action 2-Select the data in cells A1-B9 When you select the data, it should be highlighted and look like the following: Now that the data is selected, Excel can easily generate different kinds of charts. The column headings should be included in data selection so that Excel can know how to label the charts it generates. Action 3 – Generate a Line Chart • Click on Insert Ribbon • Use Line Chart Pull down menu • Select the first option which is a simple line chart This will generate a line chart with the Sales on the Y axis and the Quarter on the X axis as shown below. 2 Looking at the graph, you can see that there is an upwards trend in the numbers. There are lots of options and different kinds of charts that can be generated from data such as this. Explore some of these options on your own time. Part 2 – Importing data from Excel into Access Often, there is data in an Excel spreadsheet that would be useful to bring into Access either as a new table or appended to an existing table. This would be much more efficient that entering the data into Access one record at a time. For our database with the customer, purchase, item, and zipcode tables, the database owner has determined that they want to be prepared for making sales anywhere in Cook County, IL and wish to have all of the zip code/city pairs entered into the database appended to the existing zipCode table.. A spreadsheet has been located containing this information. Rather than entering each of those pairs in manually one record at a time, it would be more efficient to import this information from the spreadsheet. Action 4 – Open “Lab9.database” in Access. This resin the database with the tables, relationships, and data. This is what will be used to demonstrate the integration capabilities between Access and Excel. Now, we will import the zip code information contained in the spreadsheet you should have downloaded. Action 5 – Click on the External Data item and select “New Data Source”. Select From File, and Click on Excel 3 Once you select Excel, you will begin a wizard that will lead you through importing the data. It will first ask you for the file name and what kind of an import. We are going to be importing zip code/city pairs and appending to the existing zip code table. Action 6 - Tell Access where the spreadsheet is and how to import the spreadsheet file. • Browse to the location of where you stored the zip codes file • Select the button to append a copy of the records • Using the pulldown menu, select the name of the ZipCode Table • Click on OK This will start up the wizard and lead you through the necessary steps. Since you are appending to an existing table, the first row of the excel file being imported should have the field names matching the field names of the table where the data is being imported. In this case, the two field names are zipCode and city and are already in the downloaded spreadsheet file. The screen shown next shows you how the data will be imported. Since the data being imported has columns that exactly match the table columns, you do not have any options to select. If you were creating a new table from a spreadsheet, you would be given additional options. While this is not part of the lab exercise, this is something you may wish to explore on your own time. 4 Action 7 – Click “Next” indicating you agree with the way the data is mapped. Once you click next, another screen will confirm what table the imported data is going to be appended. You do not need to enter any data on this screen, just click on “Finish”. Action 8 – Click on finish to complete the import 5 The wizard will then display the next screen and if you wish to save the import steps. This would be useful if you had a consistent set of data that you wished to import on some regular basis. For the lab exercise, we will leave this unchecked. We can then close the wizard. Action 9 – Click on Close to finish the wizard. If you open up the ZipCode table in Access you will see that the data from the spreadsheet is now contained in the table. It is also possible to import data to Access from a delimited text file, another Access DB, other databases, or various other sources. Check these out on your own time. Part 3 – Exporting results from an Access Query to an Excel Spreadsheet As you learned in Lab Exercise 6, Access provides the ability to generate data based on some user defined criteria. This data can be pulled from a single table or multiple tables. Obtaining data from multiple tables requires that the relationships between tables have been appropriately established in Access. If we can create a useful set of data from the tables in Access, then it would be useful to be able to export that data to Excel since Excel has a lot more flexibility to do different kinds of analysis. For instance, with the Access file we are working with for this lab, it would be useful to be able to analyze all of the sales information including the customer’s last name, city, date of purchase, quantity, item name, item price and sales amount. 6 In the Access file, a query has been provided that pulls information on those items from the appropriate tables and creates a dataset for all sales. The screen below shows the design of the provided query. This has been provided to you, you do not have to recreate it. You will notice that there are no criteria specified. That is because we want to produce a dataset of all the sales information. Action 10 – Generate a dataset using the provided query Use the “PurchaseQuery” in the left hand panel to generate the dataset by double clicking it. The screen below shows where that is located and the resulting dataset. LastName, city, date, quantity, itemname, and itemprice are pulled directly from the appropriate tables. Sales Amount is a calculated field obtained by multiplying Quantity and itemPrice. Now we have a dataset that includes all of the information about Sales in the database. It would be useful to be able to export this dataset to Excel for further analysis on product sales, sales to different customers, sales on given dates, etc. Generally this kind of analysis is easier to perform in Excel than in Access. 7 Action 11 – Begin Export Wizard • Click on the External Data Ribbon • In the Export area of the ribbon, Click on Export to Excel This will start up the export wizard and display the following screen: Action 12 – Setup Export Wizard • Ensure that the file name is the file name you want created by the export • Ensure the File Format is set up as Excel Workbook • Check the box to export data with formatting and layout. This will make sure that the data in Excel is formatted the same as it was in Excel. • Check the box to open the destination file when export complete. This will bring you up in Excel after completion of the operation. • Click OK When you click OK, it will execute the export and since you had the “open destination file” box checked, it will bring up excel with the dataset you exported. This is what you should see. 8 Now we can do various analysis on this data using the capabilities of Excel. As an example, calculate the average sales amount from column G. Action 13 – Find average sales amount in Excel. In cell F10, enter the text “Average”, in cell “G10”, enter the function “=Average(G2:G9)” and you should get the following result. On your own time, you can try other kinds of analysis on this data. Now that we have this information in Excel, we can use some of the powerful analysis and display capabilities in pivot tables and pivot charts. Pivot charts provide a graphical display of the information in the pivot table. Charts are often a very useful way to display data. 9 Action 14 – Create a Pivot Table and Pivot Chart • Select the Insert Ribbon • Click on Pivot Chart towards the middle • Use the pull down menu under Pivot Chart to select the option of creating both a pivot table and a pivot chart Action 15 – Select Pivot Table Options for Creation • Select the appropriate range for your pivot table source. In the case of the lab exercise this should be A1:G9 which includes the header row. • Indicate you want a new worksheet created • Click OK to create the new worksheet with the pivot table and the pivot chart This will create both a pivot table and a pivot chart which can be used to do some of the same type of analysis you did in Lab Exercises 3 and 4. The new worksheet in your workbook file should look as follows: 10 Now we can many of the same kind of analysis that we did with Pivot Tables and also generate various graphical displays. For purposes of this lab exercise we will do one. Action 16 – Determine the amount of sales of each item in each city. • Drag Sales Amount to the “∑ Values” field • Drag Cities to the “Axis” field • Drag ItemName to the “Legend” field This is what you should see (note, you may have to adjust the position of the pivot chart in case it is overlapping with the pivot table. This would give you information about which items had the most sales in each city. 11 As time permits, try some other fields in the different categories of the Pivot Chart to see what each looks like and consider how that data might be useful. Being able to combine use of Access and Excel brings together the strengths of both tools. Access in managing and storing the data and Excel in having powerful analysis tools that help you understand, visualize and communicate the information. 12 zipCode 60004 60005 60006 60007 60008 60009 60016 60017 60018 60019 60022 60025 60026 60029 60038 60043 60053 60055 60056 60062 60065 60067 60068 60070 60074 60076 60077 60078 60082 60090 60091 60093 60094 60095 60107 60130 60131 60133 60141 60153 60154 60155 60159 60160 60161 60162 city Arlington Heights Arlington Heights Arlington Heights Elk Grove Village Rolling Meadows Elk Grove Village Des Plaines Des Plaines Des Plaines Des Plaines Glencoe Glenview Glenview Golf Palatine Kenilworth Morton Grove Palatine Mount Prospect Northbrook Northbrook Palatine Park Ridge Prospect Heights Palatine Skokie Skokie Palatine Techny Wheeling Wilmette Winnetka Palatine Palatine Streamwood Forest Park Franklin Park Hanover Park Hines Maywood Westchester Broadview Schaumburg Melrose Park Melrose Park Hillside 60163 60164 60165 60168 60169 60171 60173 60176 60179 60192 60193 60194 60195 60196 60202 60203 60204 60208 60209 60296 60297 60301 60302 60303 60304 60305 60402 60406 60409 60411 60412 60415 60419 60422 60425 60426 60428 60429 60430 60438 60439 60443 60445 60452 60453 60454 60455 Berkeley Melrose Park Stone Park Schaumburg Hoffman Estates River Grove Schaumburg Schiller Park Hoffman Estates Hoffman Estates Schaumburg Schaumburg Schaumburg Schaumburg Evanston Evanston Evanston Evanston Evanston Mchenry Mchenry Oak Park Oak Park Oak Park Oak Park River Forest Berwyn Blue Island Calumet City Chicago Heights Chicago Heights Chicago Ridge Dolton Flossmoor Glenwood Harvey Markham Hazel Crest Homewood Lansing Lemont Matteson Midlothian Oak Forest Oak Lawn Oak Lawn Bridgeview 60456 60457 60458 60459 60461 60462 60463 60464 60465 60466 60467 60469 60471 60472 60473 60475 60476 60477 60478 60480 60482 60483 60487 60499 60501 60513 60525 60526 60534 60546 60558 60601 60602 60603 60604 60605 60606 60607 60608 60609 60610 60611 60612 60613 60614 60615 60616 Hometown Hickory Hills Justice Burbank Olympia Fields Orland Park Palos Heights Palos Park Palos Hills Park Forest Orland Park Posen Richton Park Robbins South Holland Steger Thornton Tinley Park Country Club Hills Willow Springs Worth Tinley Park Tinley Park Bedford Park Summit Argo Brookfield La Grange La Grange Park Lyons Riverside Western Springs Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago 60617 60618 60619 60620 60621 60622 60623 60624 60625 60626 60628 60629 60630 60631 60632 60633 60634 60636 60637 60638 60639 60640 60641 60643 60644 60645 60646 60647 60649 60651 60652 60653 60654 60655 60656 60657 60659 60660 60661 60663 60664 60666 60668 60669 60670 60673 60674 Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago 60675 60677 60678 60679 60680 60681 60682 60684 60685 60686 60687 60688 60689 60690 60691 60693 60694 60695 60696 60697 60699 60701 60706 60707 60712 60714 60803 60804 60805 60827 Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Chicago Harwood Heights Elmwood Park Lincolnwood Niles Alsip Cicero Evergreen Park Riverdale ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors