Data Modeling and Product Table
Review Assignments
Data Files needed for the Review Assignments: Supplier.accdb (cont. from Tutorial 1) and Goods.xlsx
In addition to tracking information about the suppliers Belmont Landscapes works with,
Oren also wants to track information about their products. He asks you to create a new table in the Supplier database by completing the following steps:
1. Open the Supplier database, which you created and stored in the Access1\Review folder in Tutorial 1.
2. Open the Company table in Design view, and set the field properties as shown in Figure 2-43.
3. Save and close the Company table. Click the Yes button when a message appears indicating some data might be lost. Switch to Datasheet view and resize columns, as necessary, to their best fi t. Then save and close the Company table.
4. Create a new table in Design view, using the table design shown in Figure 2-44.
5. Specify ProductID as the primary key, and then save the table as Product.
6. Modify the table structure by adding a new field named Unit (data type: Text, field size: 15) between the Price and Color fields. Move the Size field so that it follows the Material field.
7. Enter the records shown in Figure 2-45 in the Product table. When finished, close the Product table.
8. Use the Import Spreadsheet Wizard to add data to the Product table. The data you need to import is contained in the Goods workbook, which is an Excel fi le located in the Access1\Review folder provided with your Data Files.
a. Specify the Goods workbook as the source of the data.
b. Select the option for appending the data.
c. Select Product as the table.
d. In the Import Spreadsheet Wizard dialog boxes, make sure Access confirms that the first row contains column headings, and import to the Product table. Do not save the import steps.
9. Open the Product table in Datasheet view and resize all columns to their best fi t.
Then save and close the Product table.
10. Define a one-to-many relationship between the
Data Files needed for the Review Assignments: Supplier.accdb (cont. from Tutorial 1) and Goods.xlsx
In addition to tracking information about the suppliers Belmont Landscapes works with,
Oren also wants to track information about their products. He asks you to create a new table in the Supplier database by completing the following steps:
1. Open the Supplier database, which you created and stored in the Access1\Review folder in Tutorial 1.
2. Open the Company table in Design view, and set the field properties as shown in Figure 2-43.
3. Save and close the Company table. Click the Yes button when a message appears indicating some data might be lost. Switch to Datasheet view and resize columns, as necessary, to their best fi t. Then save and close the Company table.
4. Create a new table in Design view, using the table design shown in Figure 2-44.
5. Specify ProductID as the primary key, and then save the table as Product.
6. Modify the table structure by adding a new field named Unit (data type: Text, field size: 15) between the Price and Color fields. Move the Size field so that it follows the Material field.
7. Enter the records shown in Figure 2-45 in the Product table. When finished, close the Product table.
8. Use the Import Spreadsheet Wizard to add data to the Product table. The data you need to import is contained in the Goods workbook, which is an Excel fi le located in the Access1\Review folder provided with your Data Files.
a. Specify the Goods workbook as the source of the data.
b. Select the option for appending the data.
c. Select Product as the table.
d. In the Import Spreadsheet Wizard dialog boxes, make sure Access confirms that the first row contains column headings, and import to the Product table. Do not save the import steps.
9. Open the Product table in Datasheet view and resize all columns to their best fi t.
Then save and close the Product table.
10. Define a one-to-many relationship between the