Reference no: EM132996550

Creating Objects for the Garden Naturally Database

Problem: Garden Naturally is a company that provides products for the organic gardening community. Sales representatives are responsible for selling to distributors, nurseries, and retail stores. The company recently decided to store its customer and sales rep data in a database. Each customer is assigned to a single sales rep, but each sales rep may be assigned many customers. The database and the Sales Rep table have been created, but the Salary YTD field needs to be added to the table. The records shown in Table 1-6 must be added to the Sales Rep table. The company plans to import the Customer table from the Excel worksheet shown in Figure 1-86. Garden Naturally would like to finish storing this data in a database and has asked you to help.

Instructions: Perform the following tasks:

1. Run Access, open the Lab 1 Garden Naturally database from the Data Files, and enable the content.
2. Open the Sales Rep table in Datasheet view and add the Salary YTD field to the end of the table. The field has the Currency data type. Assign the caption SR # to the Sales Rep Number field.
3. Add the records shown in Table 1-6.
4. Resize the columns to best fit the data. Save the changes to the layout of the table.
5. Import the Lab 1-1 Customer workbook shown in Figure 1-86 into the database. The first row of the workbook contains the column headings. Customer Number is the primary key for the new table. Assign the name Customer to the table. Save the Import steps, and assign the name Import-Customer Workbook to the steps. Assign Import Customer Workbook as the description.

6.Open the Customer table in Design view and make the following changes:

1. Change the field size for the Customer Number field to 4. Change the field size for the Customer Name field to 30. Change the field size for the Address field to 25 and the field size for the City field to 20. Change the field size for the State field to 2 and the field size for the Postal Code field to 5. Change the field size for the Sales Rep Number field to 2.
2. Add the caption CU # to the Customer Number field.
3. Add the caption SR # to the Sales Rep Number field.

7.Save the changes to the Customer table. If a Microsoft Access dialog box appears with the 'Some data may be lost' message, click the Yes button.

8.Open the Customer table in Datasheet view and resize all columns to best fit the data. Save the changes to the layout of the table.

9.Create the report shown in Figure 1-87 for the Customer table. The report should include the Customer Number, Customer Name, Amount Paid, Balance Due, and Sales Rep Number fields. Include totals for the Amount Paid and Balance Due fields. Be sure to change the column headings to those shown in Figure 1-87. Save the report as Customer Financial Report.