Hands-On Exercises

Download as a Word document: Hands-On Exercise Ch10 (114.0K)

Whiz Kids P.C. Repair: Exchanging Data with Excel

Whiz Kids P.C. Repair uses Access for its database functions. However, the accounting department generally uses Excel because of its superior financial functionality. In this exercise, Eddie will export a query to an Excel worksheet in order to send it to accounting. He will then import a worksheet into the database. Assume the role of Eddie and perform the steps that he identifies.

Download the AC09XTRA01 database file and the AC09XTRA02 Excel file and save them to your personal storage location.
Open the AC09XTRA01 database file.
First, create an Excel workbook using the information in the query qryOrders.
CLICK: Queries button in the Database window
SELECT: qryOrders in the list area
To launch the Office Links feature:
CLICK: down arrow attached to the Merge It with Microsoft Word button ( )
CLICK: Analyze It with Microsoft Excel button ( )
After a few seconds, a new workbook is opened and displayed in Microsoft Excel. Notice that the workbook is given the same name as its query object. Your screen should appear similar to Figure 10.1.

Figure 10.1
Sending data from Access to Microsoft Excel

Save the workbook to your personal storage location and then close it.
The accounting department, using their preferred Excel format, occasionally updates the prices in the products table. Eddie must import this information.
CHOOSE: File, Get External Data, Import
SELECT: Microsoft Excel in the Files of Type drop down box
DOUBLE-CLICK: the AC10XTRA02 Excel file in your personal storage location.
The Import Spreadsheet Wizard will start. Proceed, choosing the following options:
- Show Sheet 1
- Check in First row contains column headers box
- Store the worksheet as a new table
- Accept the default field names
- Choose ProductID as the primary key.
- For now, accept Sheet1 as the table name.
You will see the sheet added as the new table Sheet1. Now, Eddie wishes to replace the tblProducts table with the new sheet.
RIGHT-CLICK: on the Sheet1 table
CHOOSE: Rename
TYPE: tblProducts
CLICK: OK command button
You will be prompted by Access to confirm that you want to overwrite the current table.
CLICK: Yes
Open the tblProducts table. It should appear similar to figure 10.2

Figure 10.2

Imported tblProducts table

Close the database.

Nikita’s Catering: Creating A Switchboard Menu

Nikki wants to add a switchboard menu to her database in order to make it easier to use.

Download the AC10XTRA03 database file and save it to your personal storage location.
Open the AC10XTRA03 database file in Access.
To create a new switchboard:
CHOOSE: Tools, Database Utilities, Switchboard Manager
CLICK: Yes command button to create a new switchboard
When the Switchboard Manager appears, click the Edit command button to begin editing the contents of the switchboard form.
Using the New command button, add two items to the switchboard. The first item, entitled "Add Function," should open the frmFunctionInfo form in Add mode. The second item, entitled "Add Customer," should open the frmCustomers form in Add mode.
When ready to proceed, close the Switchboard Manager and return to the Database window.
To test the switchboard form:
CLICK: Forms button in the Objects bar
DOUBLE-CLICK: Switchboard
Your screen should appear similar to Figure 10.3

Figure 10.3
Creating a switchboard

One at a time, click the menu items and close the Form window that appears. Close the Main Switchboard form before proceeding.
Now, Nikki will make the switchboard open every time the database is opened. She will accomplish this with a Macro.
CLICK: Macros button in the Objects bar
CLICK: New button ( ) on the Database window toolbar
In the Action column, choose to open and display a form:
CLICK: down arrow attached to the Action text box
SELECT: OpenForm
Notice that the Action Arguments area displays some text boxes for customizing the OpenForm action
In the Action Arguments area, specify the form that you want to display. Do the following:
CLICK: in the Form Name text box
CLICK: down arrow attached to the text box
SELECT: Switchboard
To save the macro with the special name "Autoexec" to have it open when the database is opened:
CLICK: Save button ( ) in the toolbar
TYPE: Autoexec
CLICK: OK command button
Your screen should now appear similar to Figure 10.3.

Figure 10.4
Autoexec macro

Close the Autoexec Macro.
Close the AC10XTRA03 database file.
Open the AC10XTRA03 database file and confirm that the switchboard opens automatically.
Close the database.

Waxing Nostalgic Records: Analyzing a Table

Al is in the process of creating an inventory database. He has kept previous inventory records in an Excel spreadsheet and imported this into his database. He now wants to use Access’s Table Analyzer Wizard to normalize the imported data.

Download the AC10XTRA04 database file and save it to your personal storage location.
Open the AC10XTRA04 database.
Open the tblAlbums table and familiarize yourself with the contents. When ready to proceed, close the datasheet window.
To launch the Table Analyzer Wizard:
CHOOSE: Tools, Analyze, Table
The initial steps in the Table Analyzer Wizard dialog box display some helpful information. After reviewing the information:
CLICK: twice
In the third step of the wizard:
SELECT: tblAlbums in the Tables list box
CLICK:
Let the wizard decide which fields go in which tables and then:
CLICK:
The Table Analyzer Wizard should appear similar to Figure 10.5.

Figure 10.5
Table Analyzer Wizard

Rename the primary table (Table1) to "newAlbums" and then rename the Table2 object to "newArtists." Proceed to the next step in the wizard by clicking .
Assign the primary key to the ID field in the newAlbums table and click .
Accept Access’s typographical corrections by first clicking and then clicking the Yes command button.
In the last step of the dialog box, choose the option to create a query representing the original table. Then:
CLICK:
Your screen should now display a datasheet and confirmation dialog box, as shown in Figure 10.6.

Figure 10.6
Completing the Table Analyzer Wizard

Close the confirmation dialog box and then close the Query datasheet window.
On your own, open the newAlbums table object to review its contents. Close the datasheet window and then the database.

Data Files: Hands-On Exercises Data Files Ch10 (310.0K)

2002 McGraw-Hill Higher Education
Any use is subject to the Terms of Use and Privacy Policy.
McGraw-Hill Higher Education is one of the many fine businesses of The McGraw-Hill Companies.