You have been asked to help your department decide how to upgrade computers. Let’s say, for example, that you want to upgrade all of the computers’ operating systems to Windows 10. Furthermore, you want to first upgrade the computers that most need upgrading, but you have a limited budget. To address this situation, you would like to query the data in Figure AE-2, find all computers that do not have Windows 10, and then select those with slower CPUs or smaller memory as candidates for upgrading. To do this, you need to move the data from Excel and into Access.

• Once you have analyzed the data and determined the computers to upgrade, you want to produce a report. In that case, you may want to move the data from Access and back to Excel or perhaps into Word.

In this exercise, you will learn how to perform these tasks.

a. To begin, download the Excel file Ch04Ex02_E7e from www.pearsonhighered.com/kroenke into one of your directories. We will import the data in this file into Access, but before we do so, familiarize yourself with the data by opening it in Excel. Notice that there are three worksheets in this workbook.

Close the Excel file.

b. Create a blank Access database. Name the database Ex21_Answer. Place it in some directory; it may be the same directory into which you have placed the Excel file, but it need not be. Close the default table that Access creates and delete it.

c. Now we will import the data from the three worksheets in the Excel file Ch04Ex02_E7e into a single table in your Access database. On the ribbon, select EXTERNAL DATA and Excel. Start the import. For the first worksheet (Denver), you should select Import the source data into a new table in the current database. Ignore the warning about the first row by clicking OK. Be sure to click First Row Contains Column Headings when Access presents your data. You can use the default Field types and let Access add the primary key. Name your table Employees and click Finish. There is no need to save your import script.

For the second and third worksheets, again click EXTERNAL DATA/Excel, but this time select Append a copy of the records to the table Employees.

Select the Miami worksheet and click Finish. Repeat to import the Boston office employees.

d. Open the Employee table, and examine the data.

Notice that Access has erroneously imported a blank line and the Primary Contact data into rows at the end of each data set. This data is not part of the employee records, and you should delete it

(in three places—once for each worksheet). The Employee table should have a total of 40 records.

e. Create a parameterized query on this data. Place all of the columns except ID into the query. In the OS column, set the criteria to select rows for which the value is not Windows 10. In the CPU

(GHz) column, enter the criterion: <=[Enter cutoff value for CPU] and in the Memory (GB) column, enter the criterion: <=[Enter cutoff value for Memory]. Test your query. For example, run your query and enter a value of 4 for both CPU and memory. Verify that the correct rows are produced.

f. Use your query to find values of CPU and memory that give you as close to a maximum of 15 computers to upgrade as possible.

g. When you have found values of CPU and memory that give you 15, or nearly 15, computers to upgrade, leave your query open. Now click External data, Word, and create a Word document that contains the results of your query. Adjust the column widths of the created table so that it fits on the page. Write a memo around this table explaining that these are the computers that you believe should be upgraded.

i. Click File, Save, and Save.

j. Take a screenshot with your name showing and paste it into your document. (You can take a screenshot by pressing Alt + Print Screen.)

k. Explain why more people don’t use LibreOffice if it’s free.

l. Explain why a systems administrator, who manages hundreds of servers (with Linux and Windows operating systems), might like using LibreOffice.

m. Explain why LibreOffice might be an important application for users or organizations in developing countries.