the rest of the files

A2010_GOV1_CAP_Instructions.docx

Office 2010 – myitlab:grader – Instructions GO! Access Capstone Project

Faculty and Classes

Project Description: In this project, you will import two existing Excel worksheets as tables into your Access database. You will create a relationship between two tables, create queries (including a crosstab query), a form, and a report with formatting.

Instructions: For the purpose of grading of the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Access. Open the downloaded database named GO_aV1_Grader_CAP. 0 2 Begin to import the downloaded GO_aV1_data2.xlsx workbook (Sheet 1) into the database as a new table. Click the check box First Row Contains Column Headings and then click Next. 7 3 With the field name Faculty ID selected, set the Indexed option to Yes (No Duplicates) and then click Next. 2 4 Set the Faculty ID field as the primary key and click Next. Change the table name to Faculty_Contact and finish the Import Spreadsheet Wizard. Close the Get External Data - Excel Spreadsheet dialog box without saving the import steps. 3 5 Open the Faculty_Contact table in Design view. Change the Middle Initial field size of the Faculty_Contact table to 2. Save and close the table. 2 6 Import the downloaded GO_aV1_data3.xlsx workbook (Sheet 1) into the database as a new table. Ensure that the First Row Contains Column Headings check box is selected and click Next. With the field name Class ID selected, set the Indexed option to Yes (No Duplicates) and click Next. 10 7 Set the Class ID field as the primary key and click Next. Change the table name to Class_List and finish the Import Spreadsheet Wizard. Close the Get External Data - Excel Spreadsheet dialog box without saving the import steps. 2 8 Open the Class_List table in Datasheet view. Sort the table in ascending order by the Class Name field. Save and close the table. 2 9 Add the Faculty_Contact and Class_List tables to the Relationships window. Create a one-to-many relationship between the Faculty ID field in the Faculty_Contact (primary) table and the Class_List (related) table. Enforce referential integrity between the two tables. In the Relationships window, save the changes. 5 10 Create a Relationship Report. Save the report as Relationships for GO_aV1_Grader_CAP. Close the report and then close the Relationships window. 2 11 Create a new query in Design view. Add the Faculty_Contact and Class_List tables to the query. From the Faculty_Contact table, add the First Name, Last Name, and Department fields (in that order). From the Class_List table, add the Class Name, Meeting Days, Begin Time, End Time, and Credit Hours (in that order). Save the query as Faculty_Schedule. 10 12 Sort the query by the Department field in ascending order. Run, save, and then close the query. 2 13 Using the Query Wizard, create a Crosstab query based on the Faculty_Schedule query. Select the Department field for the row headings and the Meeting Days field for the column headings. Set the query to count the class names by Department and then by Meeting Days. Accept all other default options. Save the query as Schedule_Crosstab and finish the wizard. Close the query. 8 14 Create a new query in Design view based on the Class_List table. Add the Department, Class Name, and Credit Hours fields (in that order). Save the query as Credit_Hours. 5 15 In Design view, set the criteria in the Credit Hours field to >4. Sort the query by the Department field in ascending order. Run the query (there should be 34 records displayed). Save and close the query. 4 16 Create a simple form based on the Class_List table that will open in Layout view. Save the form as Class_Form. 4 17 In Layout view of the form, format the form title as 22 pt and bold. 3 18 View the form in Design view. Change the height of the Form Footer to 0.5". Insert a label so that it is aligned with the top left corner of the footer. Enter text Created by: Dean of Students in the label. Save the form. 5 19 View the form in Form view. Add a new record as follows: Enter C48 as the Class ID, Spanish as the Department, Spanish Level 2 as the Class Name, Mon/Weds as the Meeting Days, 8:00 AM as the Begin Time, 11:00 AM as the End Time, and 6 as Credits. Close the form. 7 20 Create a simple report based on the Faculty_Contact table that will open in Layout view. Save the report as Faculty_Report. 4 21 Delete the Faculty ID, Middle Initial, and Birthday columns. Double-click the right edge of the First Name and Last Name labels to give the column widths the best fit automatically. 5 22 Select the entire Department column, then drag it to the left of the First Name column so it is the first column in the report. Group the report by the Department column. 4 23 Set the theme of only the Faculty_Report to Opulent. Sort the report by the Last Name field in ascending order. Print Preview the report. Save and close the report. 4 24 Close all database objects. Close the database and then exit Access. Submit the database as directed. 0 Total Points 100

Updated on10/07/2010 1 A2010_GOV1_CAP_Instructions.docx