Question

1. Download the EXCEL file: Access Exercise Tables 2. Open a new blank database in ACCESS and name it “Exercise-Your Name” where you replace Your Name with your name. 3. Import each worksheet in the EXCEL file into ACCESS as a separate table as follows: a. External Data Tab -> Import Excel icon b. In the dialog box browse for the destination of the excel file you saved in step 1, it should default to “import the source data in to a new table in the current database”. Click OK. c. A wizard will start, so step through the screens for each worksheet i. Select the specific worksheet to import, click Next. ii. Check the box “First row contains headings”, click Next. iii. Do not modify field in the wizard, click Next. iv. Choose No Primary Key (we will do this later), click Next. v. Save the table as the name of the tab of worksheet, click Finish. d. Repeat for all four worksheets 4. Open each table in design view a. Select the appropriate key field and create a primary key (highlight the correct field name and then click on the little key icon on the toolbar) b. For the tblSales-Inventory, be sure to select both the Invoice Number and Item Number as the primary key (click on one, then hold down the CTRL key while clicking on the second to highlight both. Then, click the key icon. 5. On the Database Tools tab go to the RELATIONSHIPS screen (click on the icon that looks like a little REA diagram) a. In the Show Table dialog box, click on each table to highlight them all, then click Add, the Close. b. Move the tables around to reflect an REA diagram. c. Link the tables by dragging a primary key from one table to the corresponding foreign key in the other table. A line should appear between the two tables. d. Close the table tabs by right clicking on them across the top, leave the relationship screen open. e. Double-click on the line and when the dialog box appears, click on Enforce Referential Integrity f. Save the Relationships after you have all four tables linked like you would with an REA diagram (if you turned the relationship Sales-Inventory into an entity). tblCustomer links to tblSales links to tblSales-Inventory links to tblInventory. g. Print the Relationship Report. 6. Click on the Create Tab. Click the Query Wizard icon. a. In the wizard’s first screen, first select simple query wizard, click OK. b. Select tblSales from the drop down menu and select all the attributes for tblSales using the >> button; then, select tblSales-Inventory and select all the attributes except the Invoice Number, using the > button, click Next. c. Select Detail query on the next wizard screen d. Continue through the wizard to display the query. e. Print the results.

Invoice Number	Date	Customer Number
100001	1/23/2005	10001
100002	1/23/2005	10005
100003	1/23/2005	10007
100004	1/26/2005	10010
100005	1/26/2005	10001
100006	1/26/2005	10003
100007	1/26/2005	10006
100008	1/28/2005	10005
100009	1/28/2005	10004
100010	1/28/2005	10001
100011	1/28/2005	10024
100012	1/29/2005	10002
100013	1/30/2005	10004

10001	Dunn Plumbing	2763 Cosgrove Road	2nd Floor	West Haven, CT 06516-1960
10002	Ace Construction Co.	3788 Spring Grove Avenue		Cincinnati, OH 45217-0830
10003	Bryant Boiler Repair	357 East Wentworth Drive	#207	Chicago, IL 60629-1597
10004	Bucknell Air Conditioning	3198 Storm Lake Road		Lewisburg, PA 17837-3285
10005	Cole & Co.	720 Conover Court	Building #4	Fargo, ND 58105-2930
10006	Burch Builders' Supply	21887 Larwood Rd.		Reno, NV 89557-0014
10007	Lin Plumbing Repair, Inc.	1297 Lambert Lane		New Orleans, LA 70148-2793
10010	Entero Construction	615 Lewis Ave.	Suite 103	Louisville, KY 40292-7319
10024	Thompson Plumbing Repairs	6743 Cahill Road		Spring Mills, PA 16875-6375
10025

Invoice Number	Item Number	Quantity	Price	Extension
100001	B4-400	10	$65.19	$651.90
100001	BT-400	14	$28.49	$398.86
100001	C8-050	50	$14.95	$747.50
100001	CC-050	30	$1.95	$58.50
100002	C4-100	50	$10.45	$522.50
100002	CL-100	20	$3.29	$65.80
100002	CT-100	10	$4.29	$42.90
100003	B4-025	20	$21.95	$439.00
100003	BC-025	50	$5.25	$262.50
100003	BT-025	10	$7.95	$79.50
100004	BL-100	100	$10.45	$1,045.00
100004	C4-300	70	$18.25	$1,277.50
100005	CT-050	25	$3.39	$84.75
100006	C4-100	100	$10.45	$1,045.00
100006	C8-200	100	$30.59	$3,059.00
100006	CL-200	50	$4.59	$229.50
100006	CT-100	20	$4.29	$85.80
100007	C8-050	200	$14.95	$2,990.00
100007	C8-100	50	$21.59	$1,079.50
100007	CC-050	100	$1.95	$195.00
100007	CT-050	50	$3.39	$169.50
100007	CT-100	20	$4.29	$85.80
100008	C4-300	20	$18.25	$365.00
100008	C8-300	10	$38.59	$385.90
100008	CC-300	4	$4.19	$16.76
100008	CL-300	6	$5.29	$31.74
100008	CT-300	4	$6.59	$26.36
100009	C8-025	100	$12.95	$1,295.00
100009	CL-025	50	$1.95	$97.50
100009	CL-050	10	$2.49	$24.90
100010	B4-100	10	$34.79	$347.90
100010	BL-100	15	$10.45	$156.75
100010	BT-100	10	$13.69	$136.90
100011	B4-050	20	$26.49	$529.80
100011	B4-200	10	$43.69	$436.90
100011	BT-050	15	$9.97	$149.55
100012	C4-025	100	$6.95	$695.00
100012	CL-025	10	$1.95	$19.50
100012	CT-025	10	$2.49	$24.90
100013	CT-025	10	$2.49	$24.90

Item Number	Description
B4-025	.25-inch Brass 4-foot pipe
B4-050	.50-inch Brass 4-foot pipe
B4-100	1.0-inch Brass 4-foot pipe
B4-200	2.0-inch Brass 4-foot pipe
B4-400	4.0-inch Brass 4-foot pipe
BC-025	.25-inch Brass Cap fitting
BL-100	1.0-inch Brass Elbow
BT-025	.25-inch Brass T-connector
BT-050	.50-inch Brass T-connector
BT-100	1.0-inch Brass T-connector
BT-400	4.0-inch Brass T-connector
C4-025	.25-inch Copper 4-foot pipe
C4-100	1.0-inch Copper 4-foot pipe
C4-300	3.0-inch Copper 4-foot pipe
C8-025	.25-inch Copper 8-foot pipe
C8-050	.50-inch Copper 8-foot pipe
C8-100	1.0-inch Copper 8-foot pipe
C8-200	2.0-inch Copper 8-foot pipe
C8-300	3.0-inch Copper 8-foot pipe
CC-050	.50-inch Copper Cap fitting
CC-300	3.0-inch Copper Cap fitting
CL-025	.25-inch Copper Elbow
CL-050	.50-inch Copper Elbow
CL-100	1.0-inch Copper Elbow
CL-200	2.0-inch Copper Elbow
CL-300	3.0-inch Copper Elbow
CT-025	.25-inch Copper T-connector
CT-050	.50-inch Copper T-connector
CT-100	1.0-inch Copper T-connector
CT-300	3.0-inch Copper T-connector

Answer 1

• On the Data tab, click Existing Connections.

  

• In the Show drop-down list, do one of the following:

  • To display all connections, click All Connections. This is selected by default.

  • To display only the recently used list of connections, click Connections in this Workbook.
    
    This list is created from connections that you have already defined, that you have created by using the Select Data Source dialog box of the Data Connection Wizard, or that you have previously selected as a connection from this dialog box.

  • To display only the connections that are available on your computer, click Connection files on this computer.
    
    This list is created from the My Data Sources folder that is usually stored in the My Documents folder on your computer.

  • To display only the connections that are available from a connection file that is accessible from the network, click Connection files on the Network.This list is created from an Excel Data Connection Library (DCL) on a Microsoft SharePoint Services site. A DCL is a document library in a SharePoint Services site that contains a collection of Office Data Connection (ODC) files (.odc). Typically, a DCL is set up by a site administrator, who can also configure the SharePoint site to display ODC files from this DCL in the External Connections dialog box. For more information, see SharePoint Services Central Administration Help.

    Tip: If you do not see the connection that you want, you can create a connection. Click Browse for More, and then in the Select Data Source dialog box, click New Source to start the Data Connection Wizard so that you can select the data source that you want to connect to.

    Note: If you choose a connection from the Connection files on the network or Connection files on this computer categories, the connection file is copied into the workbook as a new workbook connection, and is then used as the new connection information.

• Select the connection that you want, and then click Open.

• In the Import Data dialog box, under Select how you want to view this data in your workbook do one of the following:

  Important: The Select how you want to view this data in your workbook section and its options, as shown in the following list, are not available for text, Web Query, and XML data connections. If you are connecting to such data, skip to step 5.

  • To create a table for simple sorting and filtering, click Table.

  • To create a PivotTable report for summarizing large amounts of data by aggregating and subtotaling the data, click PivotTable Report.

  • To create a PivotTable report and PivotChart report for visually summarizing data, click PivotChart and PivotTable Report.

  • To store the selected connection in the workbook for later use, click Only Create Connection.

    Use the Only Create Connection option to store the selected connection in the workbook for later use. For example, if you are connecting to an Online Analytical Processing (OLAP) cube data source and you intend to convert PivotTable cells to worksheet formulas by using the Convert to Formulas command (On the Options tab, in the Tools group, click OLAP tools), you can use this option because you don't need to save the PivotTable report.

• Under Where do you want to put the data?, do one of the following:

  • To place the PivotTable or PivotChart report in an existing worksheet, select Existing worksheet, and then type the address of the first cell in the range of cells where you want to locate the PivotTable report.

  • Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog .

• To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

• Optionally, you can change connection properties by clicking Properties, then making your changes in the Connection Properties, External Data Range, or XML Map Properties dialog boxes, and then clicking OK.