New Perspectives Access 2019 | Module 1: SAM Project 1b Ridgedale College CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS GETTING STARTED Open the file NP_AC19_1b_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

New Perspectives Access 2019 | Module 1: SAM Project 1b

Ridgedale College

CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS

GETTING STARTED

• Open the file NP_AC19_1b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_1b_FirstLastName_2.accdb by changing the “1” to a “2”.
  • If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• 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 are a graduate teaching assistant for Ridgedale College, a private college in the Midwest. To help the college better manage their course scheduling, you have created an Access database and are working on the tables.
   
   Create a new table in Datasheet View with the following options:
   1. Rename the ID field to DeptID and change the data type to Short Text.
   2. Change the field size of the DeptID field to 4.
   3. Add a new field with the name DeptName and the Short Text data type. Save the table and use Departments as the table name.
       
2. With the Departments table still open in Datasheet View, add the seven records shown in Table 1. Save and close the Departments table.

Table 1: New records for Departments Table

 

DeptID	DeptName
ACCT	Accounting
BUS	Business
CIS	Computer Science
ECON	Economics
ENGR	Engineering
MATH	Math
MKT	Marketing

 

3. Create a new table in Design View to store the teaching assistants at Ridgedale College as follows:
   1. Add a field with the name TAID and the AutoNumber data type.
   2. Make TAID the table's primary key. Save the table with the name TeachingAssistants but do not close it.
4. With the TeachingAssistants table still open in Design View, add a second and third field to the table with the field names TAFirstName and TALastName, each with the Short Text data type. Save the TeachingAssistants table and then close it.
5. Open the Professors table in Design View and use ProfFirstName as the new field name for the FirstName field and use ProfLastName as the new field name for the LastName field.
6. With the Professors table still open in Design View, change the data type property for the ProfEMail field from Short Text to Hyperlink.
7. With the Professors table still open in Design View, add a new field to the Professors table after the ProfEMail field. The field should be named HireDate and have the Date/Time data type. Save the Professors table but do not close it.
8. Switch the Professors table to Datasheet View, navigate to the 12th record (which has a ProfID field value of LRG for Lucas Garland), and delete the record.
9. With the Professors table still open in Datasheet View, navigate to the fifth record (which has a ProfID field value of CJZ for Carla Zimmerman) and edit the value for the Salary field, using 49000 as the new value. Close the Professors table.
10. Use the Form Wizard to create a form for the Professors table with the following options:
    1. Include all fields from the Professors table in the form.
    2. Select Columnar as the layout for the form. Save the form with the name ProfessorEntry as shown in Figure 1. Close the form.

Figure 1: ProfessorEntry Form in Form View

11. Use the Simple Query Wizard to create a query based on the Classes table with the following options:
    1. Include all fields from the Classes table in the query. Show detailed records. Save the query with the name ClassListing and then close the query.
12. Use the Report Wizard to create a report based on the Classes table with the following options:
    1. Include all of the fields from the Classes table in the report.
    2. Do not include any grouping fields.
    3. Sort the report by ClassNo in ascending order.
    4. Select Tabular as the layout of the report and Portrait as the orientation. Save the report with the name Classes and preview the report as shown in Figure 2.  Close the Classes report.

Figure 2: Classes Report in Print Preview

13. In the Navigation Pane, rename the Sections form. Use SectionsByProfessor as the new name of the form.

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.