Solve This

At this time, an auto-graded version of this project is not available in MyLab IT.

College Database

You help in the registrars office at your college. You are being asked to help update the colleges database by modifying some of the tables and by creating some queries and reports. You will use Microsoft Access to complete this exercise.

You will use the following skills as you complete this activity:

  Create and Modify Data Table

  Modify Field Properties

  Import Excel Data

  Establish Table Relationships

  Create AND Query

  Create Multitable Query

Instructions

1.  Open the TIA_Ch11_Start file and save the database as TIA_Ch11_LastFirst.

2.  Open the Faculty table in Design View. Change the Data Type of FullTime to Yes/No. Add a primary key to the FacultyID field. Move the DeptID field to be after the Title field.

3.  In the Faculty table, modify the Field Size and Caption properties as follows:

Field Name

Field Size

Caption

FacultyFirst

10

First Name

FacultyLast

15

Last Name

DeptID

3

Dept ID

FullTime

Full Time Status

4.  Add a new field with the name HireDate and the Date/Time data type. Save and close the Faculty table.

5.  On the External Data tab, click New Data Source, click From File, then select Excel in the Import & Link group. Browse to your student data files and select the TIA_Ch11_Faculty.xlsx file. In the Specify how and where you want to store the data in the current database section, click Append a copy of the records to the table: and select the Faculty table. Accept all other default options in the wizard. Close the Get External Data – Excel Spreadsheet wizard, and then open the Faculty table to ensure the records were imported correctly. You should see 261 records display. Close the Faculty table.

6.  Open the Students table in Datasheet view. Adjust the columns so the column sizes best fit the data in each field. Then, in Design view of the Students table, change the data type for the Email field to Hyperlink. Add a Default Value of CT for the State field. Save and close the Students table.

7.  Create a form using the Form Wizard using all fields from the Students table. Select a Columnar layout and name the form Students. In the Students form, click in the Student Last text box. Then, using the search box in the Navigation bar at the bottom of the Student form, type Lopez. Check that the StudentID is 444096, and then update the address field to 4024 Broad Street to reflect the students new address. Save and close the Students form.

8.  On the Database Tools tab, click Relationships. Add the Faculty table and create a relationship between the Faculty table and the Courses table using the FacultyID field. Enforce referential integrity. Save and close the Relationships window.

9.  Create a query from the Courses table using Query Design. Display the CourseID, Building, Room, Days, Start, and End fields. Enter criteria to display records for courses meeting in the Park building, on Tuesdays and Thursdays. You should see 78 records display. Save the query as Park TTH. Run, then close the query.

a.  Hint: Check how Days are displayed in the table and use the same format for Days in the criteria.

10. Create a query using Query Design that includes CourseID from the Roster table; Course Description from the Courses table; FacultyLast from the Faculty table; and StudentLast, StudentFirst, and Email from the Students table. Display only MGT110 IC courses. Save the query as MGT110 IC Roster. Run, then close the query. You should have 4 records display.

11. Using the Report Wizard, create a report from the MGT110 IC Roster query. Include all fields except CourseID and FacultyLast. View the data by Courses; do not add additional grouping. Sort by StudentLast in Ascending order. Accept the default layout options and save the report as Rosters.

12. Save and close the database and submit based on your instructors directions.