CGS ACCESS

Create a new blank form in Design View. Save the form with the name 'OperatingExpenseForm'

Click the 'Create' tab. In the Forms group, click 'Form Design'. Click the 'Save' button on the Quick Access Toolbar. Type 'OperatingExpenseForm' in the Form name box and click 'OK.'

Change the Navigation Pane grouping option so all database objects of the same type are grouped together (all tables together, all forms together, etc.)

Click the top of the Navigation Pane to display the category and group list. In the Navigate to Category section, select 'Object Type'.

From SQL view, modify the criteria to return only records where the value of the DeptName field in the Department table is 'Marketing'. Run the query to see the results.

Edit the line of code that begins with 'WHERE'. Delete 'Economics' and type 'Marketing' instead. On the Query Tools Design tab, in the Results group, click the 'Run' button.

Add the 'OpenQuery' action to this macro. It is located in the 'Filter/Query/Search' folder. Set 'Tuition' as the query to open when the macro is run.

In the Action Catalog, click the arrow next to 'Filter/Query/Search'. Double-click 'OpenQuery'. Expand the 'Query Name' list, and select 'Tuition'.

Display the group footer

In the group, Sort, and Total Pane, click the 'More' button. Click the arrow next to 'without a footer' section, and select 'with a footer section' instead.

Move the comment so it appears before the OpenQuery action in the macro

Move the mouse pointer over the comment title, and then click the 'Move up' arrow

Delete the second 'OpenQuery' action from this macro

Move the mouse pointer over the second 'OpenQuery' action. Click the 'X'.

Open the Macro Builder to create a new stand-alone macro.

On the Create tab, in the 'Macros & Code' group, click the 'Macro' button.

Create a new navigation form with horizontal tabs

On the Create tab, in the Forms group, click the 'Navigation' button. Click 'Horizontal Tabs'.

Create a new navigation form with horizontal tabs. Add the 'NewStudent' form to the first placeholder in the navigation form.

On the Create tab, in the Forms group, click the 'Navigation' button. Click 'Horizontal Tabs'. Click the 'NewStudent' form in the Navigation Pane and drag it to '[Add New]' in the navigation form.

Use the wizard to create a query to display records from the 'Students' table without matching records in the 'Faculty' table. Select the 'Advisor' field as the field that might contain matches. Include the 'Student ID' and 'LastName' fields (in that order) in the query results. Accept the suggested query name, and view the query results when finished.

On the Create tab, in the Queries group, click the 'Query Wizard' button. Click 'Find Unmatched Query Wizard' and click 'OK'. Click 'Table:Students'. Click 'Next'. Click 'Table: Faculty'. Click 'Next'. Click 'Next'. Double-click 'StudentID'. Double-click 'LastName'. Click 'Next'. Click 'Finish'.

Create a new blank report in Design view. Save the report as 'Students'.

On the Create tab, in the Reports group, click the 'Report Design' button. Click the 'Save' button on the Quick Access Toolbar. Type 'Students' in the Report name box and click 'OK' to save the report.

Run the 'Performance Analyzer' on all database objects at once. Accept the 'recommendation' and allow Access to make the change for you. When you are finished , close the Performance Analyzer

On the Database Tools tab, in the Analyze group, click the 'Analyze Performance' button. Click the 'All Object Types' tab. Click the 'Select All' button. Click 'OK'. Click the recommendation to add an index to the 'TuitionRates' table. Click the 'Optimize' button. Click the 'Close' button.

Modify the table properties to display the message "Due date cannot be before invoice date" if the table validation rule is violated.

On the Design tab, in the Show/Hide group, click the "Property Sheet" button. In the Validation Text box, type message "Due date cannot be before invoice date" and then press "Enter".

From Design view, hide the navigation buttons for the subform

On the Form Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. Click anywhere in the subform to select the subform control, and then click the form selector button at the upper left corner of the subform. On the Property Sheet Format tab, in the 'Navigation Buttons' box, expand the list, and select 'No'.

Modify this query so results are grouped by values in the DeptName field and values in the 'Credits' field are summarized with the 'Sum' function. Run the query.

On the Query Tools Design tab, in the Show/Hide group, click the 'Totals' button. In the Total row, under Credits, expand the 'Group by' list and select 'Sum'. On the Query Tools Design tab, in the Results group, click the 'Run' button.

From Design view, align the selected controls at the 'Top'.

On the Report Design Tools Arrange tab, in the Sizing & Ordering group, click the Align button, and select 'Top'.

From Design View, modify the selected controls so they are aligned at the 'left' side

On the Report Design Tools Arrange tab, in the Sizing and Ordering group, click the 'Align' button and select 'Left'

From Design view, group the records in the report by values in the 'DeptCode' field

On the Report Design Tools Design tab, in the Grouping & Totals group, click the 'Group & Sort' button. Click the 'Add a group' button in the Group, Sort, and Total pane. Click 'DeptCode'.

Hide duplicate values for the selected field

On the Report Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. On the Property Sheet Format tab, click in the 'Hide Duplicates' property box, and change the selection to 'Yes'.

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

On the Report Layout Tools Design tab, in the Grouping & Totals group, click 'Group & Sort'. Click 'Add a group'. Click 'DOB'. Click the 'More' button next to DOB in the Group, Sort, and Total pane. Click the arrow next to 'by quarter' in the group options bar. Click the 'by year' radio button. Click outside the menu to accept the change.

From Layout view, create a new conditional formatting rule for the selected field. If the field value is 'greater than or equal to' the value in the 'RequiredCredits' field, apply 'bold, dark blue formatting'. Dark blue is the second color from the right in the last row of the color palette.

On the Report Layout Tools Format tab, in the Control Formatting group, click the 'Conditional Formatting' button to open the Conditional Formatting Rules Manager. Click the 'New Rule' button. In the New Formatting Rule dialog, in the Format only cells where the: section, expand the middle box and select 'greater than or equal to'. In the next box type: '[RequiredCredits]' Click the 'Bold' button. Click the 'Font Color' button to expand the color palette and click 'Dark Blue' (the second color from the right in the last row). Click 'OK'. Click 'OK' again to close the Conditional Formatting Manager.

Add a new clause to the end of the SELECT statement to sort the records alphabetically by values in the LastName field from the 'Professor' table. The ';' at the end of the WHERE clause has been deleted for you. Do not forget the semi-colon at the end of the SELECT statement. Run the query to view the results.

Type this line of code: 'ORDER BY Professor.LastName;' On the Query Tools Design tab, in the Results group, click the 'Run' button.

From SQL view, add the 'DeptName' field from the 'Department' table to the end of the SELECT clause, and then run the query to see the results.

At the end of the first line of code, type; ', Department.Deptname'. Do not forget the comma. On the Query Tools Design tab, in the Results group, click the 'Run' button.

In the Navigation Pane, select the query that will copy records that meet the query criteria and add them to another table.

Click 'ClassesByDept' query

In the Navigation Pane, select the query that will delete records in the underlying table

Click 'DeleteClasses' query

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'.

Click in the 'Criteria' row in the DeptCode column and type '[Enter Department Code]'. Click the 'Run' button. Type 'FIN' when prompted. Click 'OK'.

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.

Click in the first empty cell in the Field row in the query grid to the right of the Credits column. On the Query Tools Design tab, in the Query Setup group, click the 'Builder' button to open the 'Expression Builder'. Double-click the 'Credits' field and type '*150' to finish the expression. Click 'OK'. Click in the new field and replace 'Expr1' with 'Tuition' as the name for the field. On the Query Tools Design tab, in the Results group, click the 'Run' button.

Modify this database so the Navigation Pane is hidden when the database is opened.

Click the "File" tab to open Backstage view. Click "Options" to open the Access Options dialog. Click "Current Database." Click the "Display Navigation Pane" check box to remove the checkmark. Click "OK."

Save a copy of this front-end database as "locked" databased using the "ACCDE" format. Name the file: "Registration_Locked"

Click the "File" tab to open Backstage. Click "Save as." In the Save Database As Column, under Advanced, click 'Make ACCDE', and then click the 'Save as' button. In the File name box, type 'Registration_Locked.' Click 'Save.'

Modify the database options to display 'University Registration' in the title bar

Click the 'File' tab to open Backstage view. Click 'Options' to open the Access Options Dialog. Click 'Current Database'. In the Application Title box, type 'University Registration'. Click 'OK.'

Set the 'Navigation Form' form to display at the start up

Click the 'File' tab to open Backstage view. Click 'Options' to open the Access Options dialog. Click 'Current Database'. Expand the 'Display Form' list, and select 'Navigation Form'. Click 'OK.'

Hide the status bar in this database

Click the 'File' tab to open Backstage. Click 'Options' to open the Access Options dialog. Click 'Current Database' in the left-hand pane. Click the 'Display Status Bar' check box to remove the checkmark. Click 'OK'.

Disable Layout view for forms and reports in this database

Click the 'File' tab to open Backstage. Click 'Options' to open the Access Options dialog. Click 'Current Database' in the left-hand pane. In the Application Options section, click the 'Enable Layout' view check box to remove the checkmark. Click 'OK.'

Open the University Registration database from the 'My Documents' folder with exclusive access.

Click the 'File' tab to open Backstage. Click the 'Open' button. Double-click 'This PC' to open the Open dialog box. In the Open dialog, click 'University Registration' once to select it. Click the arrow on the 'Open' button, and select 'Open Exclusive'

Open the 'University Registration' database from the 'My Documents' folder with exclusive access and add the password 'warner'.

Click the 'File' tab to open Backstage. Click the 'Open' button. Double-click 'This PC' to open the Open dialog box. In the Open dialog, click 'University Registration' once to select it. Click the arrow on the 'Open' button, and select 'Open Exclusive'. Click the File tab. Click the 'Encrypt with Password' button. Type 'warner' in the Password box. Press 'Tab'. Type 'warner' in the Verify box. Click 'OK.'

Disable the design functions for tables in Datasheet view

Click the 'File' tab to open backstage. Click 'Options' to open the Access Options dialog. Click 'Current Database' in the left-hand pane. Click the 'Enable design changes for tables in Datasheet view' check box to remove the checkmark. Click 'OK.'

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

Click the 'File' tab. Click 'New'. Click the 'Updated:Students' icon. Type 'Students' in the File Name box. Click the 'Create' button.

This database has been opened with exclusive access. Remove the password from the database. The password is 'warner'.

Click the 'File' tab. Click the 'Decrypt Database' button. Type 'warner'. Click 'OK'.

Open the Navigation Pane

Click the 'Shutter Bar Open/Close' button

In the Navigation Pane, select the query that will copy records that meet the query criteria to a new table

Click the 'Tuition' query

In the Navigation Pane, select the query that will update records in its underlying table

Click the 'UpdateTuitionRates' query.

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

Click the arrow at the top of the 'CourseDescription' column. Point to 'Text Filters', and select 'Contains...' Type 'Foundation' in the CourseDescription contains box. Click 'OK'.

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.

Click the arrow at the top of the 'DOB' column and select 'Sort Newest to Oldest'. Click the arrow at the top of the 'Classification' column and select 'Sort A-Z'

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'.

Click the arrow at the top of the 'Department' column and click the '(Select All)' check box to remove all the checkmarks. Click the 'Economics' check box to add a checkmark. Click 'OK.' Click the arrow at the top of the 'Credits' column and click the '(Select All)' check box to remove all the checkmarks. Click the '4' check box to add a checkmark. Click 'OK.'

Switch the Navigation Pane to display the 'Grades' custom category. Add the 'StudentGPAForm' form to the 'GPA Forms' group.

Click the arrow at the top of the Navigation Pane and select 'Grades' in the Navigate to Category section. Right-click 'StudentGPAForm' form, point to 'Add to group...' and select 'GPA Forms'.

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.

Expand the 'Data Type' list for the 'Gender' field, and select 'Lookup Wizard...' Click the 'I will type in the values that I want' radio button. Click 'Next'. In the first cell under Col 1, type 'Male'. Press 'Tab'. Type 'Female.' Click 'Next'. Click the 'Limit to List' check box. Click 'Finish'.

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.

Expand the 'Tables/Queries' list and select 'Table: Departments'. Double-click 'DeptName'. Expand the 'Tables/Queries' list and select 'Table: Courses'. Double-click 'CourseNumber' and then 'CourseDescription'. Expand the 'Tables/Queries' list again and select 'Table: Classes'. Double-click 'Classcode' and 'Time'. Click 'Next'. Click 'Next'. Click 'CourseNumber' and then click the '>' button. Click 'Next'. Expand the '1' box and select 'Time'. Click 'Next' Click 'Next'. Type 'CoursesByDepartment' in the box and click 'Finish'.

From Design view, change the data type for the 'ResidenceAssignment' field to use a lookup list. The values in the list should include 'ResidenceName' from the 'ResidenceHalls' table. Items in the list should sort alphabetically by the 'ResidenceName' field. Allow Access to hide the key column. Enforce data integrity, and do not allow deletions that would violate the integrity of records. Save the table.

Expand the Data Type list for the ResidenceAssignment field and select 'Lookup Wizard'. Click 'Next'. Click 'Table: ResidenceHalls'. Click 'Next'. Double-click 'ResidenceName'. Click 'Next'. Expand the first sort box, and click 'ResidenceName'. Click 'Next'. Click 'Next'. Click the 'Enable Data Integrity' check box. Click 'Finish' Click 'Yes'.

Hide the group header

In Group, Sort, and Total pane, click the 'More' button. Click the arrow next to 'with a header' section, and select 'without a header section' instead.

Add a comment to this macro with the text: 'This macro runs a query that calculates tuition.

In the Action Catalog, double-click 'Comment'. Type: 'This macro runs a query that calculates tuition'. Click outside the comment.

The selected field, Terms, is a lookup field. Modify the lookup field properties so data entry is limited to items on the list.

In the Field Properties pane, click the 'Lookup' tab. Click in the 'Limit to List' box. Expand the list and select 'Yes'.

Export the 'Housing' query to a tab-delimited text file. Include field names in the export. You do not need to change the location of the saved file or save the export steps.

In the Navigation Pane, click the 'Housing' query once to select it. On the External Data tab, in the Export group, click the 'Text File' button. Click 'OK'. Click 'Next'. Click the 'Tab' radio button. Click the 'Include Field Names on First Row' check box. Click 'Next'. Click 'Finish'. Click 'Close'.

Export the 'Tuition' query to Excel, maintaining all data formatting and layouts. You do not need to change the location of the saved file or save the export steps.

In the Navigation Pane, click the 'Tuition' query once to select it. On the External Data tab, in the Export group, click the 'Excel' button. Click the 'Export data with formatting and layout' check box. Click 'OK'. Click 'Close'.

Add criteria to this summary query to include only records where the value in the 'Days' field is 'TTh'. Do not include the Days field in the query groups. Run the query.

In the Total row, under Days, expand the 'Group by' list and select 'Where'. In the Criteria row, under Days, type 'TTh'. On the Query Tools Design tab, in the Results group, click the 'Run' button.

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 the Form view when you are finished.

On the Create tab, in the Forms group, click the 'Form Wizard' button. Expand the 'Tables/Queries' list and select 'Table; Departments'. Double-click 'DeptCode' and then 'Deptname'. Expand the 'Tables/Queries' list and select 'Table; Courses'. Double-click 'CourseNumber' and 'Course Description'. Click 'Next'. Click 'Next'. Click 'Next'. Click 'Finish'.

Create a new query in the 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.

On the Create tab, in the Queries group, click the 'Query Design' button. In the Show Table dialog, double-click 'Students' and 'ResidenceHalls'. Click 'Close'. Double-click these fields in the Students table in this order: 'LastName', 'FirstName', 'Classification' and 'ResidenceHall'. Double-click these fields in the ResidenceHalls table in this order: 'ResidenceName' and 'FreshmenOnly'. On the Query Tools Design tab, in the Results group, click the 'Run' button.

Use the wizard to create a query to find RAs who have been assigned to more than one residence hall. Find all the records with duplicate values in the 'RAStudent' field of the 'ResidentAdvisors' table. Include the 'ResidenceAssignment' field in the query results. Accept the suggested name for the query and view the results when you are finished.

On the Create tab, in the Queries group, click the 'Query Wizard' button. Click 'Find Duplicates Query Wizard' and click 'OK'. Click 'Table:ResidentAdvisors'. Click 'Next'. Double-click 'RAStudent'. Click 'Next'. Double-click 'ResidenceAssignment'. Click 'Next'. Click 'Finish'.

Create a new crosstab query using the Crosstab Query Wizard. Select the 'ScheduleByDepartment' query. Select the 'DeptName' field as the row headings. Select the 'Days' field as the column headings. Calculate the 'Sum' of the values in the 'Credits' field. Accept the suggested name for the query.

On the Create tab, in the Queries group, click the 'Query Wizard' button. In the 'New Query' dialog, click 'Crosstab Query Wizard'. Click 'OK.' Click the 'Queries' radio button. Click 'ScheduleByDepartment'. Click 'Next.' Double-click 'DeptName'. Click 'Next.' Click 'Days'. Click 'Next.' Click 'Sum'. Click 'Next'. Click 'Finish.'

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'.

On the Create tab, in the Tables group, click the 'Table Design' button. Type 'RA ID' Press 'Tab'. Expand the Data Type list and select 'AutoNumber'. Press 'Tab' again. Type 'Unique RA ID'. Click the 'Primary Key' button. Click the 'Save' button on the Quick Access Toolbar. Type 'Resident Advisors' in the Table Name box. Click 'OK'.

Create a new form based on the 'Details' form application part

On the Create tab, in the Templates group, click the 'Application Parts' button. In the Blank Forms section, click 'Details'.

Open the tool to see recommendations for optimizing database performance.

On the Database Tools tab, in the Analyze group, click the 'Analyze Performance' button.

Open the wizard that will analyze a table and move redundant data into new tables that are linked to a copy of the original table through lookups

On the Database Tools tab, in the Analyze group, click the 'Analyze Table' button

Open the wizard to have Access analyze the 'Student' table. Accept the recommendations. Rename 'Table2' to 'StudentYear'. Accept the primary key recommendation. Do not allow Access to create a query.

On the Database Tools tab, in the Analyze group, click the 'Analyze Table' button. Click 'Next'. Click 'Next'. Click 'Student'. Click 'Next'. Verify that the 'Yes, let the wizard decide' radio button is selected. Click 'Next'. Click 'Table2'. Click the 'Rename Table' button. Type 'StudentYear' and click 'OK'. Click 'Next'. Click 'Next'. Click the 'No, don't create the query' radio button and click 'Finish'.

Generate documentation for the 'Student' table. Review the options and verify that the documentation will include information about the table properties and relationships only and all the details for fields and indexes.

On the Database Tools tab, in the Analyze group, click the 'Database Documenter' button. Click the 'Student' check box. Click the 'Options...' button. Click 'OK.' Click 'OK.'

Split this database into separate front end and back end files. Name the back end file: 'Registration_be'.

On the Database Tools tab, in the Move Data group, click the 'Access Database' button. Click the 'Split Database' button. Click the 'Split' button. Click 'OK'.

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.

On the Database Tools tab, in the Relationships group, click the 'Relationships' button. Click 'EmployeeID' in the Professor table and drag to 'Advisor' in the Student table. In the Edit Relationships dialog, click the 'Enforce Referential Integrity' check box. Click 'Create'.

Enter a validation rule for the table to require values in the 'DueDate' field to be 'greater than' the values in the 'Date' field. Remember to enclose the field names in square brackets.

On the Design tab, in the Show/Hide group, click the 'Property Sheet' button. Type: '[DueDate]>[Date]' in the Validation Rule box. Press 'Enter'.

Create a linked table to link to the 'TuitionRates' table in the 'Finance' database.

On the External Data tab, in the Import & Link group, click the 'New Data Source' button select 'From Database', and click 'Access'. Click the 'Browse' button. Click 'Finance', and click the 'Open' button. Click the 'Link to the data source by creating a linked table' radio button. Click 'OK'. Click 'TuitionRates'. Click 'OK'.

Import data from the 'OperatingExpenses' CSV file to a new table. Use the first row as field names. Do not change any field information. Use the 'AccountID' field as the primary key. Accept the new table 'OperatingExpenses'. Do not save the import.

On the External Data tab, in the Import & Link group, click the 'New Data Source' button, select 'From File' and click 'Text File'. Click the 'Browse' button. Click the 'OperatingExpenses' file. Click 'Open'. Click 'OK'. Click 'Next'. Click the 'First Row Contains Field Names' check box. Click 'Next'. Click 'Next'. Click the 'Choose my own primary key' radio button. Click 'Next'. Click 'Finish'. Click 'Close'.

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.

On the External Data tab, in the Import & Link group, click the 'New Data Source' button, select 'From File', and click 'Excel'. Click the 'Browse' button. Click the 'Accounting' file. Click 'Open'. Click 'OK'. Click 'Next'. Click the 'First Row Contains Column Headings' check box. Click 'Next'. Click 'Next'. Click the 'Choose my own primary key radio' button. Click 'Next'. Click 'Finish'. Click 'Close'.

Import data from the 'NewAccountsPayable' tab-delimited text file and append the records to the 'AccountsPayable' table. The first row in the text file is the header row. Save the import steps. Use the default name by Access.

On the External Data tab, in the Import & Link group, click the 'New Data Source' button, select 'From File', and click 'Text File'. Click the 'Browse...' button. Click 'NewAccountsPayable', and then click 'Open'. Click the 'Append a copy of the records to the table:' radio button, and verify that 'AccountsPayable' is selected. Click 'OK'. Click 'Next'. Click the 'First Row Contains Field Names' check box. Click 'Next'. Click 'Finish'. Click the 'Save Import Steps' check box. Click the 'Save Import' button.

From Design view, add a Command button control to the upper right corner of the form above the Classification field. Use the 'Add New Record' action from the Record Operations category. Display this text on the button: 'Save and New' Name the button control: 'btnNewRecord'.

On the Form Design Tools Design tab, in the Controls group, click the 'Button' button. Click in the upper right corner of the form. Click 'Record Operations' in the Categories list. Click 'Add New Record' in the Actions list. Click 'Next'. Click the 'Text:' radio button. Edit the button text to 'Save and New' and then click 'Next'. Name the button control: 'btnNewRecord'.

From Design view, add the 'University Logo' image file to the right side of the form header, adding the image to the 'Image Gallery' at the same time.

On the Form Design Tools Design tab, in the Controls group, click the 'Insert Image' button to open the Image gallery. Click 'Browse...' Click the 'UniversityLogo' image file, and then click the 'OK' button. Click at the far right side of the form header.

From Design view, add a subform control to the bottom of this form. Base the subform on the 'Housing' form. Accept the first suggested link between the form and subform. Finish the subform without changing the subform name.

On the Form Design Tools Design tab, in the Controls group, expand the Controls gallery and click the 'Subform/Subreport' button. Click in the empty area of the Detail section at the bottom of the form. Click the 'Use an existing form' radio button. Click 'Housing' Click 'Next' Click 'Next'. Click 'Finish'.

From Design view, add the 'DormRoom' image file in an 'image control' to the form Detail section just below the ResidenceName controls.

On the Form Design Tools Design tab, in the Controls group, expand the Controls gallery by clicking the 'More' button. Click the 'Image' button. Click in the form below the ResidenceName controls. Click the 'DormRoom' image file, and then click the 'OK' button.

From Design view, add a subform control to the bottom of this form.

On the Form Design Tools Design tab, in the Controls group, expand the Controls gallery by clicking the 'More' button. Click the 'Subform/Subreport' button. Click in the empty area at the bottom of the form.

Add a subform to the bottom of this form to display all the fields from the 'Classes' table. Accept the relationship suggested by Access, and accept the suggested name for the subform

On the Form Design Tools Design tab, in the Controls group, expand the Controls gallery by clicking the 'More' button. Click the 'Subform/Subreport' button. Click in the empty area at the bottom of the form. Click 'Next'. Click the '>>' button. Click 'Next'. Click 'Next'. Click 'Finish'.

From Design view, modify this form's properties to 'not' allow new records

On the Form Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. Click in the 'Allow Additions' box, expand the list, and select 'No.'

From Design view, modify the form's property to restrict data entry to new records only

On the Form Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. Click in the 'Data Entry' property box, expand the list, and select 'Yes'.

From Design view, disable the 'CreditHourFee' bound text control so users cannot edit data in the field

On the Form Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. Click the 'CreditHourFee' bound text control. On the Property Sheet Data tab, click in the 'Enabled' box, expand the list, and select 'No'.

From Design view, disable both the vertical and horizontal scroll bars for this form.

On the Form Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. Click the 'Property Sheet Format' tab. Click in the 'Scroll Bars' property box, expand the list, and select 'Niether'.

From Design view, change the 'Border Style' property for the 'Classes subform' control to 'Transparent'.

On the Form Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. Expand the 'Selection type' box at the top of the Property Sheet, and select 'Classes subform'. On the Property Sheet Format tab, click in the 'Border Style' box, expand the list, and select 'Transparent'.

Change the tab order in this form, so the 'FirstName' control comes before the 'LastName' control in the tab order.

On the Form Design Tools Design tab, in the Tools group, click the 'Tab Order' button. Click the row selector next to 'FirstName'. Hold down the mouse button and drag to between the 'StudentID' and 'LastName' rows. Release the mouse button. Click 'OK'.

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.

On the Form Layout Tools Design tab, in the Tools group, click the 'Add Existing Fields' button. In the Field List pane, click and drag 'DOB' from the list of fields under Students to the right of the LastName controls in the form.

Run the 'OpenTuitionQuery' macro.

On the Macro Tools Design tab, in the Tools group, click the 'Run' button.

Change the query to an 'append' query to copy records to the 'ClassArchive2016' table. Run the query to copy the records to the table.

On the Query Tools Design tab, in the Query Type group, click the 'Append' button. Expand the Table Name list, and select 'ClassArchive2016'. Click 'OK'. Click the 'Run' query. Click 'Yes'.

Change the query to a 'delete' query and then run the query to delete the records

On the Query Tools Design tab, in the Query Type group, click the 'Delete' button. Click the 'Run' button. Click 'Yes'.

Change the query to a 'make table' query. Name the new table 'Tuition2016' and then run the query to make the table

On the Query Tools Design tab, in the Query Type group, click the 'Make Table' button. In the Make Table dialog, type 'Tuition2016'. Click 'OK.' On the Query Tools Design tab, in the Results group, click the 'Run' button. Click 'Yes.'

Make this query an 'update' query to increase values in the 'CreditHourFee' field by '25%'. Use the expression '[CreditHourFee]*1.25'. Run the query to update the values.

On the Query Tools Design tab, in the Query Type group, click the 'Update' button. In the Update To row, type: '[CreditHourFee]*1.25' in the CreditHourFee column. On the Query Tools Design tab, in the Results group, click the 'Run' button. Click 'Yes'.

Switch to the view where you can modify the SQL code for the query.

On the Query Tools Design tab, in the Results group, click the View button arrow and select 'SQL view'

From Design view, modify the selected controls so they are the same size as the 'tallest' control selected.

On the Report Design Tools Arrange tab, in the 'Sizing & Ordering' group, click the 'Size/Space' button, and select 'To Tallest'.

From Design view, add a subreport control to the bottom of the Detail section of this report. Base the subreport on the "CurrentHousing" report. Use the "ID" field to link the report and subreport. Finish the subreport without changing the subreport name.

On the Report Design Tools Design tab, in the Controls group, click the "Subform/Subreport" button. If the controls gallery is not visible on the ribbon, click the "Controls" button to display it. Click in the Detail section below the "ID" control. Click the "Use an existing report or form radio" button. Click "CurrentHousing." Click "Next." Click "Next." Click "Finish."

From Design view, add a subreport to the Detail section of this report. Display all the fields from the 'Class' table. Accept the relationship suggested by Access, and accept the suggested name for the subreport.

On the Report Design Tools Design tab, in the Controls group, click the 'Subform/Subreport' button. If the Controls gallery is not visible on the Ribbon, click the 'Controls' button to display it. Click in the Detail section below the 'EmployeeID' control. Click 'Next'. Click the '>>' button. Click 'Next'. Click 'Next'. Click 'Finish'.

Add a calculated control 'with a label' to the right side of the 'Report Footer' section to calculate the 'sum' of the 'Credits' field * the 'CreditHourFee' field + the 'LabFee' field for the entire report.

On the Report Design Tools Design tab, in the Controls group, click the 'Text Box' button. If the Controls gallery is not visible on the Ribbon, click the 'Controls' button to display it. Click at the far right side of the Report Footer section. On the Report Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. On the property Sheet Data tab, click in the 'Control Source' property, and type: '=SUM([Credits]*[CreditHourFee]+[LabFee]) and press 'Enter'.

Add a calculated control 'without a label' to the right of the LabFee control in the 'Detail' section to calculate the value of the 'Credits' field * the 'CreditHourFee' field + the 'LabFee' field for each individual record

On the Report Design Tools Design tab, in the Controls group, click the 'Text Box' button. If the Controls gallery is not visible on the Ribbon, click the 'Controls' button to display it. Press the 'Ctrl' key and click in the report to the right of the 'LabFee' field. On the Report Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. On the Property Sheet Data tab, click in the 'Control Source' property, and type: '=[Credits]*[CreditHourFee]+[LabFee] and press 'Enter'.

From Design view, set the 'ClassSchedule' query as this report's record source. Do not use a keyboard shortcut.

On the Report Design Tools Design tab, in the Tools group, click the 'Property Sheet' button. From the Property Sheet Data tab, click in the Record Source box, expand the list, and select 'ClassSchedule'.

Add a new conditional formatting to the selected field to display data bars. Use the default data bars settings.

On the Report Layout Tools Format tab, in the Control Formatting group, click the 'Conditional Formatting' button to open the Conditional Formatting Rules Manager. Click the 'New Rule' button. In the New Formatting Rule dialog, in the Select a rule type: list, click 'Compare to other records'. Click 'OK'. Click 'OK' again to close the Conditional Formatting Manager.

Rename the table 'IncomingFreshmenStudents' to: 'NewStudents'

Right-click 'IncomingFreshmenStudents'. Click 'Rename'. Type 'NewStudents'. Press 'Enter'.

Display the report sections that appear at the beginning and end of the report

Right-click an empty area of the report or one of the section bars, and select 'Report Header/Footer'.

Display the form header and form footer sections

Right-click any empty area of the Form Detail section and select 'Form Header/Footer'

Open the 'Run Tuition Query' macro so you can edit it in the Macro Builder

Right-click the 'Run Tuition Query' macro in the Navigation Pane and select 'Design View'.

Add a new custom category named 'Grades' to the Navigation Pane. Add a group named 'GPA Forms' within the new custom category.

Right-click the top of the Navigation Pane and select 'Navigation Options...' In the Navigation Options dialog, click the 'Add Item' button under the Categories list. Type 'Grades' and press 'Enter'. Click the 'Add Group' button under the Groups list. Type 'GPA Forms' and press 'Enter'. Click 'OK'.

From Design view, add a field validation rule to the 'AccountID' field to require that values are 'less than 2000'

Type '<2000' in the Validation Rule box in the Field Properties pane. Press 'Enter'.

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' (without punctuation). Run the query to view the results.

Type '<90' in the Criteria row in the Credits column. Type '<90' in the Or row in the Credits column. Type 'Jr' in the Criteria row in the Classification column. Type 'Sr' in the Or row in the Classification column. On the Query Tools Design tab, in the Results group, click the 'Run' button.

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

Type 'Like "A*"' in the Criteria row in the LastName column. On the Query Tools Design tab, in the Results group, click the 'Run' button.