Case Problems

Download as a Word document: Case Problems Ch08 (196.0K)

You’ve been asked to analyze the Computer Equipment inventory for the plant. The file contains data on systems, monitors and other peripherals. The inventory is stored in the file named EX08CP01 as shown in Figure 8-4. Save the file as "Assets" to your personal storage location.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch08_Image1cp.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (50.0K)</a>

Figure 8-4

You will use advanced criteria filter to manipulate the data stored. You will need to answer a number of questions based on the data stored.

• Insert four rows at the top of the worksheet
• Copy the field header row, in row five to row one.
• With the row still selected, zoom to show the selection.
• Name the criteria range (A1 to L4) Crit.
• Create a filter to find all items made by Hewlett-Packard or Compaq. How many items?
• Remove previous filter.
• Create a filter to find all items purchased before July 10, 1998. How many items?
• Remove the filter.
• Are there any items purchased from vendor 1 before July 10, 1998?
• How many items were not made by Hewlett-Packard or Compaq?

Save the file, close and exit Excel.

Data File: EX08CP01 (35.0K)

Roscommon Hardware and Gifts

Roscommon Hardware and Gifts owner, John Smith, want to find out about the people who work for the company. He gave you the file named EX08CP02 to work with. After opening, save the file as "Personnel" to your personal storage location. Then perform the following steps:

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch08_Image2cp.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (50.0K)</a>

Figure 8-5

• Use the sort dialog box to organize the list by HireDate within each Department. Print the results, if you have a printer attached to your system.
• Next, insert 4 rows at the top of the worksheet to act as a criteria area for advanced queries. Copy the field header row in row 5 to the top row.
• The original store opened June 20, 1972, who were the original employees? (Use a filter) Print the results if you have a printer attached.
• Use Database functions to answer the following: what is the average age of employees, oldest Employee, and youngest employee.

When finished save the file, close and exit Excel.

Data File: EX08CP02 (14.0K)