Microsoft Access & Excel Help Needed

Application Exercises 523

j. Click Next two times. In the box under What title do you want for your form? enter Cus- tomer Query Form and click Finish.

k. Enter Maple in the dialog box that appears. Access will open a form with the values for Maple, Rex. At the bottom of the form, click the right-facing arrow and the data for the second customer named Maple will appear. What is that customer's first name? will appear.

I. Close the form. Select Object Type and Forms in the Access Navigation Pane. Double- click the Customer Query Form and enter the value Amanda. Access will display data for all three customers having the value Amanda in their name.

Chapter 4 AE4- l. . .. , Sometimes you will have data in one Office application and want to move it to

- i'-- another Office application without rekeying it. Often this occurs when data was created for one purpose but then is used for a second purpose. For example, Figure AE-2 presents a portion of an Excel spreadsheet that shows the assignment of computers to employees.

Suppose you want to use this data to help you assess 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 suppose 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 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 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 Ch04Ex0l_Ul0e.xlsx into one of your directo-

ries. 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 Ch04Ex0l_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 Iile Ch04Ex0 I_ UlOe.xlsx into a single table in your Access database. On the ribbon, select Exter- nal Data and in the Import & Link section, click Excel. Start the import. For the first

~ 8 C D __ l_, G tll l :Em.J>LiastName EmpFtrstN-am.e Plant compurerarand CPU1(G~z) Memory1(GBJ Dlsk,{TB) OS

1 li\sh'lev Linda Oerwer Dell 3 16 2 Windows 1'0 3 ~O.awiOs.o 111 Vkitor O;e:n1;1.er Dell 3 12 2 Wiodows"'l.'0

4 Ching Diem fhi Denver ~p 3 ,8 2jiWinclows.S

s Collins James Der.iv.er Dell 2:S t6 1 \l.;tindows 7 6 Corning li.a!.ey Oerwer liiP 3 ,8 2Windows.8 7 ""!xou Richard Denver liiP 25 '8 2A5 Windo11;,s8 8 ieor.oliic Anna Denver Dell 4 .12 3Winillo-ws 10 9 jlarne Kathy Denver Lenovo 2:S 16 1·windows 7 lO jWei James Denser ISM 3 1'6 2 rt.,iiodo.ws '1'0

11 'Oi!ll:on Mary OeAtJi!r ISM 2 t6 1 Windows 7

12 Lee M:a.tith1.ew Oe-nver Dell 25 t6 1 Win<:lows 7 13 'Duong Steven Denser 'Dell 0,7:5 Vis:ta

'14 Bosa William Oenw~r llP 3 :8 .25 Windoo.>s8

L~Dr.ew fony O.eAv.er llP 3 8 2 Windov,•s .8

,,---.. 16 lAdams Mark D:er.nt.er tilP 2.5 <I 1 \,.JiodGWS 7 FIGUREAE-2 17 JL1undle111 Nic-ol.e Oeneer Ienoeo 4 '12 3 mnoows·1:0

18 iU.tr.an Bryan Denver 0,11 3 '8 2 Windo-ws:S Sample Excel Data for Import 19 a Source: Microsoft Corporation 201 P-r'Jrnarv .CO J;J:tact: l(.ayeO-.al/JCS-Olil

524 Application Exercises

worksheet (Denver), you should select Import the source data into a new table in the cur- rent 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 Miami and Boston worksheets, again click External Data, Import 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.

c. 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 CPU and 10 for 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 maxi- mum of 10 computers to upgrade as possible.

g. When you have found values of CPU and memory that give you 10, or nearly 10, 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.