New Perspectives Access 2019 | Module 3: SAM Project 1b Ridgedale College CREATING QUERIES, SORTING RECORDS, CHANGING THE APPEARANCE OF THE DATASHEET GETTING STARTED Open the file NP_AC19_3b_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

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

Ridgedale College

CREATING QUERIES, SORTING RECORDS, CHANGING THE APPEARANCE OF THE DATASHEET

GETTING STARTED

• Open the file NP_AC19_3b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_3b_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 queries to help make reliable decisions.
   
   Create a query based on the Professors and Grants tables in Design View with the following options:
    
   1. Add the ProfFirstName and ProfLastName from the Professors table and the GrantName and AverageAward fields from the Grants table to the design grid in that order.
   2. Sort the records in ascending order by ProfLastName.
   3. Save the query using ProfGrants the query name.
      
      Open the query in Datasheet View to view the selected data, and then close it.
2. Open the ProfessorAssignments query in Design View and make the following changes to the query:
   1. Delete the Time field from the query design grid.
   2. Add criteria to select only those records where the ClassNo field value equals CIS162.
   3. Save the changes to the ProfessorAssignments query.
      
      Open the query in Datasheet View to view the selected data, and then close it.
3. Open the MondayOr4 query in Design View and add criteria to select only those records where the Credits field value equals 4 or the MeetingDay field value equals M. Save the changes to the query. Open the query in Datasheet View to view 26 selected records, and then close the query.
4. Open the DepartmentOfferings query in Design View and make the following changes to the query:
   1. Add the DeliveryMethod field from the Sections table to the query design grid immediately after the Description field.
   2. Add criteria to select only those records where the Credits field value is less than or equal to 3.
   3. Open the query in Datasheet View and sort the records by Description in ascending order.
   4. Edit the first record changing the Description field value to Accounting for Nonprofit Organizations as shown in Figure 1.
      
      Save and close the DepartmentOfferings query.

Figure 1: DepartmentOfferings Query in Datasheet View

5. Open the Hybrid query in Design View and make the following changes:
   1. Move the Credits field to the first column of the query design grid.
   2. Sort the records in descending order by the Credits field and in ascending order by ClassNo.
      
      Save the query. Open the query in Datasheet View as shown in Figure 2. Close the Hybrid query.

Figure 2: Hybrid Query in Datasheet View

6. Open the QuinnOnline query in Design View and add criteria to select only those records where the ProfLastName field value equals Quinn and the DeliveryMethod field value equals Online. Open the QuinnOnline query in Datasheet View, and then save and close the QuinnOnline query.
7. Open the PMCourses query in Design View and add criteria to select only those records where the Time field value is greater than 12:00pm. Open the query in Datasheet View, and then save and close the query.
8. Create a new query in Design View based on the TeachingAssistants and Sections tables with the following options:
   1. Add the TeachingAssistants table and the Sections table to the design window.
   2. Add the TALastName field from the TeachingAssistants table to the design grid.
   3. Add the ClassNo, MeetingDay, and Time fields from the Sections table to the design grid.
   4. Save the query, using TASections as the name.
      
      Open the query in Datasheet View to view the selected records and then close it.
9. Open the 2CreditSections query in Design View and add criteria to select only those records where the Credits field value equals 2. Save the query. Open the query in Datasheet View to view the selected records, and then close the 2CreditSections query.
10. Open the TuitionByCourse query in Design View. Modify the query by creating a calculated field. Enter Tuition: [Credits]*110 in the Zoom dialog box of the first empty column in the design grid. Save the query. Open the query in Datasheet View to view the calculated field values, and then close the TuitionByCourse query.
11. Open the Sections table in Datasheet View. Use the Find and Replace feature to find records with a DeliveryMethod field value of Face-to-face and use Traditional as the replacement for all occurrences of Face-to-face. Close the Sections table.
12. Open the Professors table in Datasheet View and make the following changes:
    1. Change the font to Times New Roman.
    2. Change the font size to 10.
    3. Sort the records in the table in ascending order by ProfLastName.
       
       Save the changes to the table.
13. With the Professors table still open in Datasheet View. Add a Calculated Currency field called Monthly that divides the Salary field by 12. Save the changes to the table and close it.
14. Open the TeachingAssistants table in Datasheet View. Sort the records in descending order by Stipend. Save the changes to the table and close it.
15. Open the Professors table in Datasheet View and hide the ProfEmail and HireDate fields that currently have no data. Save the changes to the table and close it.

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.