MOAC Access 9Advanced TablesStudent Assessment Projects
Competency Assessments
Project 9-1: Summarize the Sales Table
The Sales table you created at Lucerne Publishing seems incomplete. Add a Totals row to summarizethe data.
USE Lucerne Publishing XXX that you saved in a previous exercise.
1. Open the Sales table.
2. On the Home tab, in the Records group, click the Totals button. The Totals row appears.
3. Click the down arrow in the Totals row of the Gross Sales column. Select Sum fromthe menu.
4. Click the down arrow in the Totals row of the Cost of Goods column. Select Sum fromthe menu.
5. Click the down arrow in the Totals row of the Net Sales column. Select Sum fromthe menu.
6. Save and close the table.
CLOSE the database.
Project 9-2: Analyze the Fourth Coffee Customers Table
In your part-time job as an office assistant at Fourth Coffee, you have been taking on most of thedatabase responsibilities. As you learn more and more about Access, you decide to use the Table Analyzerto check a table you created previously to make sure it is efficient.
GET READY. LAUNCH Access if it is not already running.
1. OPEN Fourth Coffee Inventory from the data files for this lesson.
2. SAVE the database as Fourth Coffee Inventory XXX (where XXX is your initials).
3. Open the Customers table.
4. On the Database Tools tab, in the Analyze group, click the Analyze Table button. The
Analyze Table Wizard dialog box appears.
5. Click Next > to display the next Analyze Table Wizard dialog box.
6. Click Next > to display the next Analyze Table Wizard dialog box.
7. The Customers table should be selected. Click Next.
8. The Yes, let the Wizard decide option button should be selected. Click Next.
9. A message is displayed that says the wizard does not recommend dividing the table.Click OK.
10. Click Cancel to close the Table Analyzer Wizard.
11. Close the table.
LEAVE the database open for use in the next project.
Proficiency Assessments
Project 9-3: Design the Fourth Coffee Sales Table
Sales data for Fourth Coffee has just come in for the first quarter. The manager asks you to create atable that displays the sales for each of the five stores in your division. Note: Each store is known by athree-digit number, such as 656.
USE the Fourth Coffee Inventory XXX database that you saved in a previous exercise.
1. Create a new table in Design View.
2. Key ID as the first field name and press the Tab key. Set the data type to AutoNumber.
3. Key Month as the second field name and press the Tab key. A message appearsstating that the word month is a reserved word. Click OK. Change the field name toMon and set its data type to Text.
4. Enter the remaining field names and data types, as shown in Figure 9-20. Set theprimary key as shown.
Figure 9-20
5. Save the table as Monthly Sales by Store.
6. Switch to Datasheet View.
7. Enter the data in the table as shown in Figure 9-21.
Figure 9-21
8. Insert a Totals row.
9. Count the Mon field and sum the Sales field.
10. Save and close the table.
CLOSE the database.
Project 9-4: Summarize the Wingtip Toys Table
As marketing coordinator at Wingtip Toys, you are constantly examining sales data and trying to thinkof ways to increase sales. Total the inventory table to get a clear picture of the current inventory.
GET READY. LAUNCH Access if it is not already running.
1. OPEN Wingtip Toys Inventory from the data files for this lesson.
2. SAVE the database as Wingtip Toys Inventory XXX (where XXX is your initials).
3. Open the Inventory table.
4. Insert a Totals row.
5. Count the Description field, sum the In Stock field, and sum the Price field.
6. Save and close the table.
LEAVE the database open for use in the next project.
Mastery Assessments
Project 9-5: Design the Wingtip Toys Yearly Sales Table
The owner of Wingtip Toys has given you yearly sales data for each of the company’s sales channels.Create a table in which to store and total the data.
USE the Wingtip Toys Inventory XXX that you saved in a previous exercise.
1. Create a new table in Design View.
2. Create the table as shown in Figure 9-22.
Figure 9-22
3. Save the table as Yearly Sales and switch to Datasheet View and review the table youjust created.
4. Switch back to Design View.
5. Insert a blank row above the Catalog field.
6. Key Yras a new field with the Text data type.
7. Select the Yrfield and click the Primary Key button to designate the Yrfield as the newprimary key.
8. Delete the ID field.
9. Save the table and switch to Datasheet View.
10. Enter data in the table as shown in Figure 9-23.
Figure 9-23
11. Save the table.
12. Insert a Totals row.
13. Sum the Catalog, Internet, Stores, and Other columns.
14. Save and close the table.
CLOSE the database.
Project 9-6: Analyze the Alpine Reservations Table
As administrative assistant for Alpine SkiHouse, you have noticed that one of the tables you use ona regular basis seems large and cumbersome and you have to enter some of the same data again andagain. You decide to run the Table Analyzer to see if the table needs to be split.
GET READY. LAUNCH Access if it is not already running.
1. OPEN Alpine Reservations from the data files for this lesson.
2. SAVE the database as Alpine Reservations XXX (where XXX is your initials).
3. Select the Reservations table in the Navigation pane.
4. Run the Table Analyzer, letting the wizard decide how to split the table.
5. Rename Table1 to Reservation Details and rename Table2 to Room Details.
6. Select the ID field in the Reservation Details table and designate it as the primary key.
7. Create the query and finish the wizard.
8. Save and close the database.
CLOSE Access.
1