1)

Sort the records in this table so they are grouped by the value in the Classification field from A to Z and then grouped within each classification by the value in the DOB field from newest to oldest. Don't forget that when sorting by multiple fields, start with the innermost sort and work your way out.

2

Create a filter using the Custom Filter dialog to show only records where the value in the CourseDescription field contains the word Foundation.

3

Use AutoFilter to filter the query results first to show only records where value in the Department field is Economics and the value in the Credits field is 4.

4

Create a new table in Design view. Name the first field: RA ID Make the field data type: AutoNumber Add the description: Unique RA ID Set the field as the primary key. Save the table with the Name: Resident Advisors

5

From Design view, modify the Gender field to use a lookup list with Male and Female in a single column. Limit the field to values in the list only.

6

Create a new relationship between the EmployeeID field in the Professor table and the Advisor field in the Student table. Ensure that values in the Advisor field have a corresponding value in the EmployeeID field.

7

Create a new query in Design view using the following fields in this order: From the Students table, add the LastName, FirstName, Classification, and ResidenceHall fields. From the ResidenceHalls table, add the ResidenceName and FreshmenOnly fields. Run the query.

8

Add criteria to this query to return records where the student LastName field begins with the letter A. Run the query.

9

Add criteria to this query to return only the records where the value in the Credits field is less than 90 and the value in the Classification field is Jr or Sr. Run the query to view the results.

10

Create a parameter query where the user will enter a value to use as the criterion for the DeptCode field. Use the prompt Enter Department Code. Run the query using the Department Code FIN.

11

Add a new calculated field named Tuition in the first empty column to the right of the Credits field. The new field should calculate the value in the Credits field multiplied by 150. Run the query to view the results.

12

Use the Form Wizard to create a new form. Include these fields in this order from the Departments table: DeptCode and DeptName fields and then add these fields from the Courses table: CourseNumber and CourseDescription. Organize the form by the Departments table with data from the Courses table as a datasheet subform. Accept the suggested names for the form and subform. Allow Access to open the form in Form view when you are finished.

13

Use the Field List pane to add the DOB field to the form immediately to the right of the LastName controls. Do not switch views.

14

Use the Report Wizard to create a new report. Include fields in this order: DeptName from the Departments table, CourseNumber and CourseDescription from the Courses table, and ClassCode and Time from the Classes table. View the report data by the Departments table. Add grouping by CourseNumber. Sort records by the Time field. Use the Stepped layout and Portrait orientation. Name the report CoursesByDepartment and allow Access to display the report in Print Preview view when you finish.

15

From Layout view, group this report by values in the DOB field. Change the grouping to group by year instead of by quarter.

16

From Layout view, add automatic totals to the GPA column to calculate the average GPA.

17

Create a new blank form that will open in Layout view.

18

Use the Field List pane to add the LastName field from the Students table to the form. Do not switch views.

19

Move the TuitionPaid label control and its bound control to the layout area directly below the FirstName controls.

20

Insert a new row into the form layout above the TuitionPaid and GPA controls.

21

Add the image UniversityLogo  to the form header as a logo. Do not switch views.

22

Add a title to the form header and change the text to: Review Press Enter when you are finished. Do not switch views.

23

Switch to the view where you can modify the structure of the report without viewing any live data.

24

Create a new blank report that will open in Layout view.

25

Use the Field List pane to add the following fields to the report: From the Departments table, add the DeptName field. From the Faculty table, add the LastName and FirstName fields. Do not switch views.

26

From Layout view, change the width of the DeptName column, so the department names do not wrap to two lines (approximately 2.3 inches).

27

Select the DOB column and then move it to place it between the FirstName and GPA columns.

28

From Layout view, add the date and time to the report header using the date format similar to 31-May-16 and the time format similar to 1:12 PM.

29

Add automatic page numbers centered at the bottom of the page footer on every page. Use the format that includes the current page number and the total number of pages. Do not change views.

30

From Layout view, group this report by values in the Classification field.

31

Modify this query to show only students who are freshmen. Add criteria to the Classification field to return only records where the classification is FR. Run the query.

32

Limit the query results to records where the value in the Credits field is greater than 120. Run the query to view the results.

33

Modify this query so the DeptCode field is hidden in the query results, and then run the query.

34

Set the sort order so the results will display records with the highest number of credits first. Run the query and review the results.

35

Add criteria to this query to return only the records where the value in the Credits field is greater than 120 and less than 130. Run the query to view the results.

36

Add criteria to this query to return only the records where the value in the Credits field is >124 and the value in the Classification field is Sr Run the query to view the results.

37

Add criteria to this query to return only the records where the value in the DeptCode field is ENG or CIS. Run the query to view the results.

38

Preview how the results of this query will look when printed when the page orientation is changed to landscape.

39

Change the Field Size property for the RAStudentID field to 4.

40

Insert a new Date/Time field named StartDate between the DegreeName and Status fields. Save the table when you are finished.

41

From Design view, apply the Medium Time format to the StartTime field.

42

Set the Default Value property for the Classification field to Fr.

43

Apply the Short Date input mask format to the selected field (StartDate). Do not change default format or placeholder characters.

44

Modify the table relationships so that when a record is updated in the Department table, the related records in the Professor table will also be updated.

45

Modify the table relationships so that when a record is deleted in the Department table, the related records in the Professor table will also be deleted.

46

Use AutoFilter to filter the query results to show only records where the value in the Department field is Accounting or Finance.

47

Create a new desktop database from the Updated: Students template. Do not change the location. Name the database: Students.

48

Create a backup of the database. You do not need to change the backup location.

49

Add a new Short Text field to the far right side of the table. Name the field: Description

50

Add the Status Quick Start fields to the right of the DegreeName field.

51

Add a new record to the table with the student ID 6412 and advance to the second field.

52

Delete this student record.

53

Import all of the fields from the Assets worksheet in the Accounting Excel file to a new table. Use the first row as column headings. Do not change any field information. Use the AccountID field as the primary key. Accept the new table Assets. Do not save the import steps.

54

Modify the width of the Time column to exactly 22.

55

Modify the width of the CourseDescription column to best fit the data.

56

Use the Find and Replace dialog to find the record with the text julie, matching the case exactly.

57

Replace all instances of the first name julie with Julie. Be sure to match the case exactly.

58

Use Filter by Selection to filter the table to show only records where the student's DOB is 10/25/1998 or later.

59

Use the Simple Query Wizard to create a select query for a single table. Include these fields in this order: LastName, FirstName, Classification, and Credits fields from the Students table. Include every record in the results. Select the option to open the query to view information. Name the query Tuition and view the query results.

60

Create a Single Record form from the Classes table.