Homework answers / question archive / Shelly Cashman Access 2016 | Module 1: SAM Project 1b All Around Outside Maintenance CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTs   GETTING STARTED Open the file SC_AC16_1b_FirstLastName_1

Shelly Cashman Access 2016 | Module 1: SAM Project 1b All Around Outside Maintenance CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTs   GETTING STARTED Open the file SC_AC16_1b_FirstLastName_1

MS Access

Share With

---

Shelly Cashman Access 2016 | Module 1: SAM Project 1b

All Around Outside Maintenance

CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTs

 

GETTING STARTED

• Open the file SC_AC16_1b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC16_1b_FirstLastName_2.accdb by changing the “1” to a “2”.
  • Hint: If you do not see the .accdb file extension in the Save As dialog box, do not type it. Access will add the file extension for you automatically.
  • To complete this Project, you will also need to download and save the following support file from the SAM website:
    • Support_SC_AC16_1b_Property.xlsx
    • Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

 

PROJECT STEPS

1. You work for All Around Outside Maintenance, an outdoor maintenance company, and have decided to create an Access database to make it easier to keep track of business information. In this project, you will create and update tables, import data, and create queries, forms, and reports.

Create a new table in Datasheet View with the following options:

1. Rename the default primary key ID field to TypeID and change the data type to Short Text. (Hint: TypeID should remain the primary key.)
2. Change the field size of the field to 3.
3. Add a new field with the name Description and the Short Text data type.

Save the table with the name ClientTypes, but do not close it.

1. With the ClientTypes table still open in Datasheet View, add the three records shown in Table 1 on the next page. If necessary, resize the Description field so that all field values are completely visible. Save and close the ClientTypes table.

 

 

Table 1: Records for ClientTypes Table

 

TypeID	Description
COM	Commercial
CRP	Corporate
RES	Residential

 

1. Open the Manager table in Datasheet View. Add a new field following the PhoneNumber field with the field name StartDate and the Date/Time data type. Close the table.
2. Open the Client table in Design View and make the following changes to the AmountPaid field:
   1. Change the data type from Number to Currency.
   2. Change the Format property to Currency.
   3. Change the Decimal Places property from Auto to 2.
   4. With the Client table still open in Design View, add Manager of client properties as the Description (Optional) to the ManagerID field.
   5. With the Client table still open in Design View, change the field size property for the ClientName field to 40. Save the changes to the Client table, and then close it. (Hint: Because there were changes to data types and field sizes, the “Some data may be lost” warning message appears. The data fits within the valid ranges, so ignore this message and continue saving the table.)
   6. Import the data from the file Support_SC_AC16_1b_Property.xlsx, available for download from the SAM website, and then append the records to the Property table. Do not create a new table, and do not save the import steps.
   7. Open the Property table in Design View and make the following changes to the PropertyID field:
      1. Make the field the primary key.
      2. Change the Caption property to PropID. (Hint: Do not type the period.)

Save the changes to the Property table, and then close the table.

1. Open the Client table in Datasheet View, navigate to the third record (which has the CL# field value CSS01), and then enter 19703 for the PostalCode field value. Navigate to the previous record (which has the CL# field value CHG05) and enter 08002 for the PostalCode field value. Close the table.
2. Open the Client Listing Query in Design View, and then add a criterion to select only those records with the State field value MD. Run the query, and then save and close the query.
3. Use the Simple Query Wizard to create a query based on the Property table with the following options:
   1. Include all fields from the Property table in the query.

Save the query with the name Property Query (which is the default name) and close it, if necessary.

1. Create a simple form for the Client table. Save the form as Client Form, and then close the form. (Hint: Use the Form button to create this form.)
2. Open the Manager Update Form in Form View and navigate to the fourth record, which has the ManagerID field value of ER01. Change the Address field value for this record to 103 Main St., and then close the Manager Update Form.
3. Create a simple report based on the Property table. Save the report as Property Report, and then close the report.
4. Open the Manager Salary Report in Layout View, and then modify the report to match Figure 1 on the next page by following these directions:
   1. Delete the PostalCode and PhoneNumber columns.
   2. Change the column heading for the ManagerID column, the FirstName column, and the LastName column to the headings shown in Figure 1.
   3. Average the values in the Salary column. If necessary, expand the size of the Average control so it displays completely.
   4. Change the report title from “Manager Salary Report” to Manager Average Salary Report. (Hint: Do not type the period.)

(Hint: Your report will display a different date and time than what is shown in Figure 1.)

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

Figure 1: Manager Salary Report