Microsoft Access NEWEST 1
Create a parameter query where the user will enter a value to use as the criterion for the InsuranceType field. Use the prompt Enter Insurance Type. Run the query using the insurance type dental.
Click in the Criteria row in the InsuranceType column and type [Enter Insurance Type]. Click the Run button. Type dental when prompted. Click OK.
Set the sort order so the results will display records with the highest Premium first. Run the query and review the results.
Click in the Sort row under the Premium field. Click the arrow to expand the sort options list, and select Descending. On the Query Tools Design tab, in the Results group, click the Run button.
Add a new calculated field named EmployeeCost in the first empty column to the right of the ProviderName field. The new field should calculate the value in the Premium field multiplied by .25. 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 ProviderName column. On the Query Tools Design tab, in the Query Setup group, click the Builder button to open the Expression Builder. Double-click the Premium field and type *.25 to finish the expression. Click OK. Click in the new field and replace Expr1 with EmployeeCost as the name for the field. On the Query Tools Design tab, in the Results group, click the Run button.
Modify this query so the InsuranceType field is hidden in the query results, and then run the query.
Click the Show box in the InsuranceType column to remove the checkmark. On the Query Tools Design tab, in the Results group, click the Run button.
From Layout view, change the width of the DeptName column, so the department names do not wrap to two lines (approximately 2.3 inches).
In the Access Report, you resized the DeptName column to 224 px by dragging cell 0-0's right border.
Add a new record to the table with the staff ID 10-9999 and advance to the second field.
In the Access Table, you clicked in Cell 13 in the StaffID column, clicked the arrow at the top of the StaffID column. You clicked the Table Column Filter Right-Click menu. In the Access Table, you clicked in Cell 13 in the StaffID column, clicked the row selector, clicked the row selector, clicked in Cell 13 in the StaffID column, typed 10-9999, clicked in the 1 Cell, and typed 10-9999.
Use Filter by Selection to filter the table to show only records where the student's DOB is 10/25/1998 or later.
In the Access Table, you clicked in the 10/25/1998 Cell, clicked in the 10/25/1998 Cell, and clicked in the 10/25/1998 Cell. In the Home Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Selection button. In the Selection menu, you clicked the On or After "10/25/1998" menu item.
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.
In the Access Table, you clicked in the Lookup Wizard Cell, clicked the Drop-Down Button, and clicked the Lookup Wizard Item. Inside the Lookup Wizard dialog, you selected the I will type in the values that I want Radio Button, clicked the Next button. Inside the Lookup Wizard dialog in the Access Table, you typed Male, typed Male and pressed Tab, and typed Female. Inside the Lookup Wizard dialog, you clicked the Next button, checked the Limit To List check box, and clicked the Finish button.
Delete the Major field from the table.
In the Access Table, you clicked in the Major Cell, dragged the Major row. In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Delete Rows button. Inside the Spa : Database dialog, you clicked the Yes button.
Set the Default Value property for the Classification field to Fr.
In the Access Table, you clicked on Classification. In the Fields Ribbon Tab in the Properties Ribbon Group, you clicked the Default Value button. Inside the Expression Builder dialog, you typed Fr in the Expression Default Value Input input, clicked the Ok button.
Create a filter using the Custom Filter dialog to show only records where the value in the CourseDescription field contains the word Foundation.
In the Access Table, you clicked on CourseDescription, clicked the arrow at the top of the CourseDescription column, right-clicked the CourseDescription column header, and clicked the arrow at the top of the CourseDescription column. In the Table Column Filter Right-Click menu in the Text Filters menu, you clicked the Contains... menu item. Inside the Custom Filter dialog, you typed Foundation in the CourseDescription contains input, clicked the OK button.
Modify the width of the CourseDescription column to best fit the data.
In the Access Table, you clicked on CourseDescription, right-clicked the CourseDescription column header. In the Right Click Menu menu, you clicked the Field Width menu item. Inside the Column Width dialog, you checked the Standard Width check box, unchecked the Standard Width check box, and clicked the Best Fit button
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.
In the Access Table, you clicked on DOB, clicked the arrow at the top of the DOB column. In the Table Column Filter Right-Click menu, you clicked the Sort Z to A menu item. In the Access Table, you clicked the arrow at the top of the Classification column. In the Table Column Filter Right-Click menu, you clicked the Sort A to Z menu item.
Use AutoFilter to filter the query results to show only records where the value in the Department field is Accounting or Finance.
In the Access Table, you clicked on Department, clicked the arrow at the top of the Department column. In the Table Column Filter Right-Click menu, you clicked the (Select All) checkbox, clicked the Accounting checkbox, clicked the Finance checkbox, and clicked the OK button.
Change the Field Size property for the RAStudentID field to 4.
In the Access Table, you clicked on RAStudentID. In the Fields Ribbon Tab in the Properties Ribbon Group, you typed 4 in the Field Size input, pressed the Enter key. Inside the Spa : Database dialog, you clicked the Yes button
Move the Start Date field so it is the last field in the table.
In the Access Table, you clicked on StartDate, clicked the arrow at the top of the StartDate column, and dragged StartDate after LastName.
Modify the width of the Time column to exactly 22.
In the Access Table, you clicked on Time, right-clicked the Time column header. In the Right Click Menu menu, you clicked the Field Width menu item. Inside the Column Width dialog, you typed 22 in the Column width: input, clicked the OK button.
Modify the width of the Time column to exactly 22.
In the Access Table, you clicked on Time. In the Home Ribbon Tab in the Records Ribbon Group, you clicked the More button. In the More menu, you clicked the Field Width menu item. Inside the Column Width dialog, you typed 22 in the Column width: input, clicked the OK button.
From Design view, apply the Medium Time format to the StartTime field.
In the Access Table, you clicked the Format property box, clicked the Drop-Down Button, and clicked the Medium Time Item.
Apply the Short Date input mask format to the selected field (StartDate). Do not change default format or placeholder characters.
In the Access Table, you clicked the Format property box, clicked the Drop-Down Button, clicked the Short Date Item, clicked in the Date/Time Cell, clicked the Drop-Down Button, dragged the StartDate row, clicked the Format property box, clicked the Drop-Down Button, clicked the Long Time Item, clicked the Format property box, clicked the Drop-Down Button, clicked the Short Date Item, clicked the Input Mask property box, and clicked the Input Mask Build... Button. Inside the Input Mask Wizard dialog from the Input Mask list, you selected Short Date. Inside the Input Mask Wizard dialog, you clicked the Next > button, clicked the Next > button, and clicked the Finish button.
Add a new Short Text field to the far right side of the table. Name the field: Description
In the Access Table, you clicked the arrow at the top of the Click To Add column. In the Drop-Down Menu menu, you clicked the Short Text menu item. In the Access Table, you typed Description and pressed Enter.
Clear the sorting from this table.
In the Access Table, you clicked the arrow at the top of the DOB column. In the Table Column Filter Right-Click menu, you clicked the Clear Filter menu item.
Sort the records in this table by the values in the DOB field, so students with the newest birth dates appear first.
In the Access Table, you clicked the arrow at the top of the DOB column. In the Table Column Filter Right-Click menu, you clicked the Sort Newest to Oldest menu item.
Use AutoFilter to filter the query results to show only records where the value in the Department field is Accounting or Finance.
In the Access Table, you clicked the arrow at the top of the Department column. In the Table Column Filter Right-Click menu, you clicked the (Select All) checkbox, clicked the Accounting checkbox, clicked the Finance checkbox, and clicked the OK button.
Delete the First Name field.
In the Access Table, you clicked the arrow at the top of the FirstName column, clicked on FirstName. In the Home Ribbon Tab in the Records Ribbon Group, you clicked the Delete button. Inside the Spa : Database dialog, you clicked the Yes button.
Clear the filter from the StaffID field.
In the Access Table, you clicked the arrow at the top of the StaffID column. In the Table Column Filter Right-Click menu, you clicked the Clear Filter menu item.
Delete this student record.
In the Access Table, you clicked the row selector. In the Home Ribbon Tab in the Records Ribbon Group, you clicked the Delete button. Inside the Spa : Database dialog, you clicked the Yes button.
Delete this student record.
In the Access Table, you right-clicked the Row Selector. In the Right-Click menu, you clicked the Delete Record menu item. Inside the Spa : Database dialog, you clicked the Yes button.
Insert a new Date/Time field named StartDate between the DegreeName and Status fields. Save the table when you are finished.
In the Access Table, you right-clicked the Status cell. In the Right Click menu, you clicked the Insert Rows menu item. In the Access Table, you typed StartDate, typed StartDate and pressed Tab, clicked the Drop-Down Button, clicked the Date/Time Item, typed Date/Time and pressed Enter, clicked in the StartDate Cell, clicked in the StartDate Cell, typed StartDate, typed StartDate and pressed Enter, typed Date/Time and pressed Enter, and typed and pressed Enter. You right-clicked the Degrees Tab Header. In the Right Click menu, you clicked the Save menu item.
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.
In the Create Ribbon Tab in the Forms Ribbon Group, you clicked the Form Wizard button. Inside the Form Wizard dialog in the Table: Courses drop-down, you selected Table: Departments. Inside the Form Wizard dialog from the Available Fields list, you selected DeptName. Inside the Form Wizard dialog, you clicked the > button. Inside the Form Wizard dialog from the Available Fields list, you selected DeptName. Inside the Form Wizard dialog, you clicked the > button. Inside the Form Wizard dialog in the Table: Courses drop-down, you selected Table: Courses. Inside the Form Wizard dialog from the Available Fields list, you selected DeptCode. Inside the Form Wizard dialog, you clicked the > button. Inside the Form Wizard dialog from the Available Fields list, you selected Credits. Inside the Form Wizard dialog, you clicked the > button, clicked the Next > button, clicked the Next > button, clicked the Next > button, and clicked the Finish button.
Create a Single Record form from the Classes table.
In the Create Ribbon Tab in the Forms Ribbon Group, you clicked the Form button.
Add a title to the form header and change the text to: Review Press Enter when you are finished. Do not switch views.
In the Create Ribbon Tab in the Forms Ribbon Group, you clicked the More Forms button. You clicked the form header, right-clicked the report area, clicked the form header, right-clicked the report area, clicked the form header, and right-clicked the report area. In the Format Ribbon Tab in the Control Formatting Ribbon Group, you clicked the Quick Styles button. In the Design Ribbon Tab in the Header/Footer Ribbon Group, you clicked the Title button. You updated the header title to Review.
Create a new blank form that will open in Layout view.
In the Create Ribbon Tab in the Forms Ribbon Group, you clicked the Navigation button, clicked the Blank Form button.
Start a new query without using one of the query wizards.
In the Create Ribbon Tab in the Queries Ribbon Group, you clicked the Query Design button.
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.
In the Create Ribbon Tab in the Queries Ribbon Group, you clicked the Query Wizard button. Inside the New Query dialog, you clicked the OK button. Inside the Simple Query Wizard dialog in the Table: Students drop-down, you selected Table: Students. Inside the Simple Query Wizard dialog from the Available Fields list, you selected DOB. Inside the Simple Query Wizard dialog, you clicked the > button. Inside the Simple Query Wizard dialog from the Available Fields list, you selected ResidenceHall. Inside the Simple Query Wizard dialog, you clicked the > button, clicked the < button. Inside the Simple Query Wizard dialog from the Available Fields list, you selected DOB. Inside the Simple Query Wizard dialog, you clicked the > button. Inside the Simple Query Wizard dialog from the Available Fields list, you selected Classification. Inside the Simple Query Wizard dialog, you clicked the > button. Inside the Simple Query Wizard dialog from the Available Fields list, you selected GPA. Inside the Simple Query Wizard dialog, you clicked the > button, clicked the Next > button, clicked the Next > button, typed Tuition in the What title do you want for your query? input, and clicked the Finish button.
Add a new table in Design view.
In the Create Ribbon Tab in the Tables Ribbon Group, you clicked the Table Design button.
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
In the Create Ribbon Tab in the Tables Ribbon Group, you clicked the Table Design button. In the Access Table, you typed RA ID, typed RA ID and pressed Tab, clicked the Drop-Down Button, clicked the AutoNumber Item, clicked in the Unique RA ID Cell, clicked in the AutoNumber Cell, typed AutoNumber and pressed Tab, and dragged the RA ID row. In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Primary Key button. You right-clicked the Table1 Tab Header. In the Right Click menu, you clicked the Save menu item. Inside the Save As dialog, you typed Resident Advisors in the Table Name: input, clicked the OK button.
Create a new table in Datasheet view. Save it with the name: insurance plans
In the Create Ribbon Tab in the Tables Ribbon Group, you clicked the Table button. In the Access Table, you typed. You clicked the Table1 Tab Header. In the application header, you clicked the Save button. Inside the Save As dialog, you typed InsurancePlans in the Table Name: input clicked the OK button.
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.
In the Database Tools Ribbon Tab in the Relationships Ribbon Group, you clicked the Relationships button. In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Edit Relationships button. Inside the Edit Relationships dialog, you clicked the dialog Close button. You clicked the relationships Database. In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Edit Relationships button. Inside the Edit Relationships dialog, you checked the Cascade Update Related Fields check box, clicked the OK button.
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.
In the Database Tools Ribbon Tab in the Relationships Ribbon Group, you clicked the Relationships button. You clicked the relationships Database, dragged the object onto the advisor in the student Database. Inside the Edit Relationships dialog, you checked the Enforce Referential Integrity check box, clicked the Create button.
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.
In the Database Tools Ribbon Tab in the Relationships Ribbon Group, you clicked the Relationships button. You clicked the relationships Database. In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Edit Relationships button. Inside the Edit Relationships dialog, you checked the Cascade Delete Related Records check box, clicked the OK button.
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.
In the Design Ribbon Tab in the Controls Ribbon Group, you clicked the Insert Image button. In the Background Image menu, you clicked the Browse... menu item. Inside the Insert Picture dialog, you clicked the dialog Close button. In the Design Ribbon Tab in the Header/Footer Ribbon Group, you clicked the Page Numbers button. Inside the Page Numbers dialog, you selected the Page N of M Radio Button, selected the Bottom of Page [Footer] Radio Button, and clicked the OK button.
From Layout view, group this report by values in the Classification field.
In the Design Ribbon Tab in the Grouping & Totals Ribbon Group, you clicked the Group & Sort button. You clicked the Add a Group button. In the Field Name menu, you clicked the Classification menu item.
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.
In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Add Existing Fields button. Inside the Field List dialog from the Tables list from the Fields available for this view: list, you expanded the Departments field. Inside the Field List dialog from the Tables list from the Fields available for this view: list from the Departments list, you selected DeptName, clicked the DeptName list item. You clicked the undefined String. Inside the Field List dialog from the Tables list from the Fields available for this view: list, you expanded the Faculty field. Inside the Field List dialog from the Tables list from the Fields available for this view: list from the Faculty list, you clicked the LastName list item. You clicked the undefined String. Inside the Field List dialog from the Tables list from the Fields available for this view: list from the Faculty list, you clicked the FirstName list item. You clicked the undefined String.
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.
In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Add Existing Fields button. Inside the Field List dialog from the Tables list from the Fields available for this view: list, you expanded the Students field. Inside the Field List dialog from the Tables list from the Fields available for this view: list from the Students list, you clicked the DOB list item. You clicked the undefined view.
Use the Field List pane to add the LastName field from the Students table to the form. Do not switch views.
In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Add Existing Fields button. Inside the Field List dialog, you clicked the Show all tables item. Inside the Field List dialog from the Tables list from the Fields available for this view: list, you selected Students, selected Students, and expanded the Students field. Inside the Field List dialog from the Tables list from the Fields available for this view: list from the Students list, you selected LastName, selected LastName, and clicked the LastName list item. You clicked the undefined view.
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.
In the External Data Ribbon Tab in the Import & Link Ribbon Group, you clicked the New Data Source button. In the New Data Source menu in the From File menu, you clicked the Excel menu item. Inside the Get External Data - Excel Spreadsheet dialog, you clicked the Browse... button. You clicked Accounting.xlsx. Inside the File Open dialog, you clicked the Open button. Inside the Get External Data - Excel Spreadsheet dialog, you clicked the OK button. Inside the Import Spreadsheet Wizard dialog, you clicked the Next > button, checked the First Row Contains Column Headings check box, clicked the Next > button, clicked the Next > button, selected the Choose my own primary key. Radio Button, clicked the Next > button, and clicked the Finish button. Inside the Get External Data - Excel Spreadsheet dialog, you clicked the Close button.
Add the Status Quick Start fields to the right of the DegreeName field.
In the Fields Ribbon Tab in the Add & Delete Ribbon Group, you clicked the More Fields button, clicked the More Fields button. In the Fields Ribbon Tab in the Add & Delete Ribbon Group, you clicked the More Fields button. In the More Fields menu, you clicked the Status menu item.
Add the Status Quick Start fields to the right of the DegreeName field.
In the Fields Ribbon Tab in the Add & Delete Ribbon Group, you clicked the More Fields button. In the More Fields menu, you clicked the Status menu item.
Rename the ID field to: DegreeName
In the Fields Ribbon Tab in the Properties Ribbon Group, you clicked the Name & Caption button. Inside the Enter Field Properties dialog, you typed DegreeName in the Name: input, clicked the OK button.
Use the Find and Replace dialog to find the record with the text julie, matching the case exactly.
In the Home Ribbon Tab in the Find Ribbon Group, you clicked the Find button. Inside the Find and Replace dialog, you typed julie in the Find What input, checked the Match Case check box, and clicked the Find Next button.
Replace all instances of the first name julie with Julie. Be sure to match the case exactly.
In the Home Ribbon Tab in the Find Ribbon Group, you clicked the Replace button. Inside the Find and Replace dialog, you typed julie in the Find What input, typed Julie in the Replace With input, checked the Match Case check box, and clicked the Replace All button. Inside the Appointments dialog, you clicked the Yes button.
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.
In the Home Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Selection button, clicked the Advanced button. In the Access Table, you clicked the arrow at the top of the Credits column. In the Table Column Filter Right-Click menu, you clicked the 3 checkbox. In the Access Table, you clicked the arrow at the top of the Department column. In the Table Column Filter Right-Click menu, you clicked the (Select All) checkbox, clicked the Economics checkbox. In the Access Table, you clicked on Credits, clicked on Department, and clicked the arrow at the top of the Department column. In the Table Column Filter Right-Click menu, you clicked the (Select All) checkbox, clicked the Economics checkbox, and clicked the OK button. In the Access Table, you clicked the arrow at the top of the Credits column. In the Table Column Filter Right-Click menu, you clicked the 3 checkbox, clicked the OK button.
Use AutoFilter to filter the query results first to show only records where value in the StaffID field is Mo and the value in the IncreaseType field is Merit.
In the Home Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Selection button, clicked the Selection button, clicked the Selection button, clicked the Advanced button, clicked the Advanced button, and clicked the Advanced button. In the Access Table, you clicked the arrow at the top of the StaffID column. In the Table Column Filter Right-Click menu, you clicked the (Select All) checkbox, clicked the Mo checkbox, and clicked the OK button. In the Access Table, you clicked the arrow at the top of the IncreaseType column. In the Table Column Filter Right-Click menu, you clicked the (Select All) checkbox, clicked the Merit checkbox, and clicked the OK button.
Switch to the view where you can modify the structure of the report without viewing any live data.
In the Home Ribbon Tab in the Views Ribbon Group, you clicked the View button arrow. In the View menu, you clicked the Design View menu item.
Export the InsuranceOptions 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 InsuranceOptions 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.
Export the InsuranceOptions query to a comma-separated 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 InsuranceOptions 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 Include Field Names on First Row check box. Click Next. Click Finish. Click Close.
Add criteria to this summary query to include only records where the value in the DepartmentName field is Management. Do not include the DepartmentName field in the query groups. Run the query.
In the Total row, under DepartmentName, expand the Group by list and select Where. In the Criteria row, under DepartmentName, type Management. On the Query Tools Design tab, in the Results group, click the Run button.
Start a new query in Design view.
On the Create tab, in the Queries group, click the Query Design button.
Create a new query in Design view using the following fields in this order: From the InsurancePlans table, add the InsuranceID, InsuranceType, SubscriptionType, and Premium fields. From the InsuranceProviders table, add the ProviderName field. Run the query.
On the Create tab, in the Queries group, click the Query Design button. In the Show Table dialog, double-click InsurancePlans and InsuranceProviders. Click Close. Double-click these fields in the InsurancePlans table in this order: InsuranceID, InsuranceType, SubscriptionType, and Premium. Double-click the ProviderName field in the InsuranceProviders table. On the Query Tools Design tab, in the Results group, click the Run button.
Use the Simple Query Wizard to create a select query for a single table. Include these fields in this order: LastName, FirstName, and DOB fields from the Staff table. Select the option to open the query to view information. Name the query Birthdays and view the query results.
On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, Simple Query Wizard is selected by default. Click OK. Expand the Tables/Queries list and select Table: Staff. Double-click each of the field names in this order: LastName, FirstName, and DOB. Click Next. Type Birthdays in the What title do you want for your query? box. Click Finish.
Import data from the Departments comma-delimited text file to a new table. Use the first row as field names. Do not change any field information. Use the DeptID field as the primary key. Accept the new table Departments. 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 Departments 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 Staff worksheet in the Staff Excel file to a new table. Use the first row as column headings. Do not change any field information. Use the StaffID field as the primary key. Accept the new table Staff. 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 Staff 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 UpdatedBenefitPlans text file and append the records to the Benefits table. The data fields in the text file are separated by tabs. The first row in the text file is the header row. Save the import steps. Use the default name suggested 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 UpdatedBenefitPlans, and then click Open. Click the Append a copy of the records to the table: radio button, and verify that Benefits 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.
Display the Total row for this query.
On the Query Tools Design tab, in the Show/Hide group, click the Totals button.
Modify this query so results are grouped by values in the InsuranceProvider field and values in the Premium 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 Premium, expand the Group by list and select Sum. On the Query Tools Design tab, in the Results group, click the Run button.
Add criteria to this query to return only the records where the value in the Premium field is <500 and the value in the SubscriptionType field is Family. Run the query to view the results.
Type <500 in the Criteria row in the Premium column. Type Family in the Criteria row in the SubscriptionType column. On the Query Tools Design tab, in the Results group, click the Run button.
Add criteria to this query to return only the records where the value in the Premium field is greater than 200 and less than 250. Run the query to view the results.
Type >200 and <250 in the Criteria row in the Premium column. On the Query Tools Design tab, in the Results group, click the Run button.
Limit the query results to records where the insurance Premium is greater than 250. Run the query to view the results.
Type >250 in the Criteria row in the Premium column. On the Query Tools Design tab, in the Results group, click the Run button.
Add criteria to this query to return only the records where the value in the InsuranceType field is Dental or Vision. Run the query to view the results.
Type Dental in the Criteria row in the InsuranceType column. Type Vision in the or row in the InsuranceType column (below the Criteria row). On the Query Tools Design tab, in the Results group, click the Run button.
Add criteria to this query to return records where the SubscriptionType field begins with the letters Self. Run the query.
Type Like "Self*" in the Criteria row in the SubscriptionType column. On the Query Tools Design tab, in the Results group, click the Run button.
Add criteria to this query to return only the records where the value in the SubscriptionType field is Self or Family and the value in the Premium field is <200. Run the query to view the results.
Type Self in the Criteria row in the SubscriptionType column. Type Family in the or row in the SubscriptionType column. Type <200 in the Criteria row in the Premium column. Type <200 in the or row in the Premium 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.
You changed the LastName field's criteria to A*. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
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.
You clicked the Classification field's criteria row, changed the Classification field's criteria to FR. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
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.
You clicked the Classification field's criteria row, clicked the Classification field's criteria row, changed the Classification field's criteria to Sr, clicked the Credits field's criteria row, and changed the Credits field's criteria to >124. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
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.
You clicked the Credits field's criteria row, changed the Credits field's criteria to < 90, clicked the Classification field's criteria row, changed the Classification field's criteria to Jr, clicked the classification field's or row, changed the classification field's or row to Sr, clicked the credits field's or row, changed the credits field's or row to <90, clicked the Credits field's criteria row, and changed the Credits field's criteria to <90. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
Limit the query results to records where the value in the Credits field is greater than 120. Run the query to view the results.
You clicked the Credits field's criteria row, changed the Credits field's criteria to >120. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
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.
You clicked the Credits field's criteria row, clicked the Credits field's criteria row, and changed the Credits field's criteria to >120 And <130. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
Set the sort order so the results will display records with the highest number of credits first. Run the query and review the results.
You clicked the Credits field's sort row, expanded the Credits field's sort options list, and selected the Descending option. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
Modify this query so the DeptCode field is hidden in the query results, and then run the query.
You clicked the DeptCode column's Show box. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
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.
You clicked the DeptCode field's criteria row, changed the DeptCode field's criteria to ENG, clicked the deptCode field's or row, and changed the deptCode field's or row to CIS. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
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.
You clicked the DeptCode field's criteria row, changed the DeptCode field's criteria to [Enter Department Code]. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button. Inside the Enter Parameter Value dialog, you typed FIN in the Enter last name input, clicked the OK button.
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.
You clicked the Drop-Down Menu, right-clicked the Drop-Down Menu. In the Right-Click menu, you clicked the Zoom... menu item. Inside the Zoom dialog, you typed Tuition:[Credits]*150 in the Zoom Value Input input, clicked the Query Zoom Ok Button button. In the Design Ribbon Tab in the Results Ribbon Group, you clicked the Run button.
Enable active content in this database.
You clicked the Enable Content button.
Go to the last record.
You clicked the Last Record Button
Change the Navigation Pane grouping option to Object Type.
You clicked the Navigation Pane Header. In the Category/Group menu, you clicked the Object Type menu item.
Change the Navigation Pane grouping option to Tables and Related Views.
You clicked the Navigation Pane Header. In the Category/Group menu, you clicked the Tables and Related Views menu item.
Open the Services table.
You clicked the Services table, double-clicked the Services table.
Open the Navigation Pane.
You clicked the Shutter Bar Open/Close button.
Insert a new row into the form layout above the TuitionPaid and GPA controls.
You clicked the TuitionPaid control. In the Arrange Ribbon Tab in the Rows & Columns Ribbon Group, you clicked the Select Row button, clicked the Insert Above button.
Add a new record to the table with the student ID 6412 and advance to the second field. 1/1
You clicked the undefined view. In the Access Table, you clicked in Cell 33 in the StudentID column, typed 4126, clicked in the 6 Cell, typed 6412, and typed 6412 and pressed Enter.
Run the Compact & Repair tool to optimize database efficiency by compacting unused space and deleting unnecessary temporary objects.
You opened the backstage view, clicked the Compact & Repair Database button.
Add a new Short Text field to the far right side of the table. Name the field: Description
You opened the backstage view, clicked the New navigation button, and clicked the back arrow button. In the Fields Ribbon Tab in the Add & Delete Ribbon Group, you clicked the Short Text button. In the Access Table, you typed Description and pressed Enter.
Create a new desktop database from the Updated: Students template. Do not change the location. Name the database: Students.
You opened the backstage view, clicked the New navigation button, clicked the Updated: Students template, and typed Students in the File Name box.
Create a new desktop database from the Updated: Students template. Do not change the location. Name the database: Students.
You opened the backstage view, clicked the New navigation button, clicked the Updated: Students template, typed Students in the File Name box, and clicked the Create button.
Preview how the results of this query will look when printed.
You opened the backstage view, clicked the Print navigation button, and clicked the Print Preview button.
Preview how this report will look when printed.
You opened the backstage view, clicked the Print navigation button, and clicked the Print Preview button.
Preview how the results of this query will look when printed when the page orientation is changed to landscape.
You opened the backstage view, clicked the Print navigation button, and clicked the Print Preview button. In the Print Preview Ribbon Tab in the Page Layout Ribbon Group, you clicked the Landscape button.
Create a backup of the database. You do not need to change the backup location.
You opened the backstage view, clicked the Save As navigation button, clicked the Back Up Database button, and clicked the Save As button. Inside the Save As dialog, you clicked the Save button.
Create a new blank desktop database named Payroll.
You opened the backstage view, clicked the back arrow button, opened the backstage view, clicked the New navigation button, clicked the Blank desktop database template, typed Payroll in the File Name box, and clicked the Create button.
