Access Practice Exam 2

Which command allows you to move a file from your computer to your OneDrive account. Select your answer from the list on the right, then click Done.

D

Which command will move the selected file to a location on your computer. Select your answer from the list on the right, then click Done.

Download

Change the query to a make table query. Name the new table 2017Insurance 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 2017Insurance, click OK. On the Query Tools Design tab, in the Results group, click the RUN button. Click YES.

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.

Delete the OpenQuery action from this macro.

Move the mouse pointer over the OPEN QUERY action. click X

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

On the Database Tools tab, in the Move Data group, click the ACESS DATABASE button. Click the SPLIT DATABASE button. CLick the SPLIT button. Click OK.

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 the empty area at the bottom of the form.

From Design view, change the Border Style property for the WageIncreases 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 the BORDER STYLE box, expand the list, and select TRANSPARENT.

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

On the Form Design Tools Design tab, in the Tools group, vlivl the PROPERTY SHEET button. Click the CreditHourFee bound test control. On the Property Sheet Data tab, click in the Enable box, expand the list, and select NO.

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.

Add a calculated control without a label to the right of the ReleaseDate control in the Detail section to calculate the value of the ReleaseDate field - the DateOfHire field for each individual record.

On the Report Design tools 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 CTRL key and click 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 the CONTROL SOURCE property, ant type: =[Credits]*[CreditHourFee]+[LabFee] and press enter.

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

Opened the backstage view, clicked the Decrypt Database button. Inside the Unset Database Password dialog, you typed griffin in the Password: input, pressed the Enter key.

Add a new custom category named Insurance to the Navigation Pane. Add a group named Insurance Forms within the new custom category.

Right-Click the top of the Navigation Plane 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 Group list. Type GPA Forms and press enter. click ok.

Display the Report Header/Footer sections.

Right-click any empty area of the form detail section and select form header/footer.

Display the form header and form footer sections.

To display the form's header section, select the Arrange tab in the toolbar at the top of the screen. Then click on the "Form Header/Footer" button in the Show/Hide group.

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

Click CLASESBYDEP query.

Open the Payroll database from the My Documents folder with exclusive access and add the password griffin.

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 PASSWPRD button. Type griffin in the Password box. Press Tab. Type griffin in the Verify Box. Click OK.

Open the Payroll 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.

Switch the Navigation Pane to display the Insurance custom category. Add the InsuranceProviders form to the Insurance 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 SQL view, modify the criteria to return only records where the value of the InsuranceType field in the InsurancePlans table is Medical. Run the query to see the results.

Edit the line of code that begins with WHERE delete the ECONIMICS and type MARKETING instead. On the Query Tools Design tab, in the Results group, click the RUN button.

From Design view, hide the navigation buttons for the subform.

Go to the form properties in design view and under the FORMAT tab look for NAVIGATION BUTTONS and select NO.

Display the group footer.

In the Group, Sort, and Total Plane, 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 first in the macro.

Move the mouse pointer over the comment title, and then click the MOVE UP arrow.

Create a new crosstab query using the Crosstab Query Wizard. Select the Wage Increases by Department query. Select the IncreaseType field as the row headings. Select the DepartmentName field as the column headings. Calculate the sum of the values in the IncreaseAmount 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 DEP-NAME. Click NEXT. Click DAYS. Click NEXT. Click SUM. Click NEXT. Click FINISH.

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

On the Create tab, in the Reports group, click the REPORT DESIGN button. Click the SAVE button on the Quick Access Toolbar. Type STAFFPAYROLL in the Report Name box and click OK to save the report.

From SQL view, add the InsuranceType field from the InsurancePlans 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.

Add a comment to this macro with the text: This macro displays staff insurance elections

In the Action, Catalog, double;e-cliick COMMENT. Type: This macro displays staff insurance elections Click outside the comment.

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

On the Create tab, iim the Macros & Code group, click the MACRO button.

Open the Run Staff Elections Report macro so you can edit it in the Macro Builder.

Right-click the RUN STAFF ELECTIONS REPORT macro in the Navigation Pane and select Design View.

Add a new clause to the end of the SELECT statement to sort the records alphabetically by values in the InsuranceType field from the InsurancePlans 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.

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

Click DeleteClasses query.

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

Click the UPDATETUITIONRATES query.

Use the wizard to create a query to display records from the Staff table without matching records in the InsurancePlans table. Accept the MedicalInsurance field as the field that might contain matches. Include the LastName and FirstName fields (in that order) in the query results. Accept the suggested query name, and view the query results when finished.

EXTRA STEP- 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 FINISHED.

Hide the group header.

In the 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.

Rename the table Work Logs to: Time Sheets.

Navigation Pane>Work Logs>Right click on Work Logs> Rename>Type in "Time Sheets"> Enter

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

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

Add a calculated control with a label to the right side of the Report Footer section to calculate the sum of the ReleaseDate field - the DateOfHire field for the entire report.

Right-click the form or report in the Navigation Pane, and then click Design View. On the Design tab, in the Controls group, click the tool for the type of control you want to create. For a list of control types that can be used as calculated controls, see the section Learn which types of controls can be used as calculated controls. Position the pointer where you want the control to be placed on the form or report, and then click on the form or report to insert the control. If a control wizard starts, click Cancel to close it. Select the control, press F4 to display the property sheet, and then type an expression in the Control Source property box. To use the Expression Builder to create the expression, click next to the Control Source property box. Switch to Form view or Report view and verify that the calculated control works as you expect.

Which command allows you to send a link to a file for others to view or edit? Select your answer from the list on the right, then click Done.

Share button

Which of the following can be used to copy a file into OneDrive from the File Explorer window? Select all that apply from the list on the right, then click Done.

You chose Ctrl + C > Ctrl + V, chose Copy button > Paste button, and clicked the Done button.

Which category would you use to customize the background for your computer? Select your answer from the list on the right, then click Done.

You chose Personalization, chose System, chose Personalization, and clicked the Done button.

Create a new blank form in Design view. Save the form with the name StaffForm.

Click the Create tab. In the forms group, click FORM DESIGN. Click the SAVE button on the Quick Access toolbar. Type StaffForm in the Form name box and click OK.

Set the NavigationForm form to display at 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.

Save a copy of this front-end database as "locked" databased using the ACCDE format. Name the file: Payroll_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 Payroll in the title bar.

Click the FILE tab to open the Backstage view. Click OPTIONS to open the access options dialog. Click Current Database. In the Application Title Box, type University Registration. CLick OK.

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.

Click the Tuition query, right-clicked the Tuition query. In the Right-Click menu in the Export Options menu, you clicked the Excel menu item. Inside the Export - Excel Spreadsheet dialog, you checked the Export with formatting and layout check box, clicked the OK button. Inside the Export - Excel Spreadsheet dialog, you clicked the Close button.

Add the OpenReport action to this macro. It is located in the Database Objects folder. Set Staff Insurance Elections as the report to open when the macro is run.

In the Actions Catalog, click the arrow next to Database Objects. Double-click open query. expand the query name list and select staff i insurance.

From Design view, change the data type for the StaffID field to use a lookup list. The values in the list should include LastName from the Staff table. Items in the list should sort alphabetically by the LastName 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 StaffID field and select Lookup Wizard¿ Click Next. Click Table: Staff. Click Next. Double-click LastName. Click Next. Expand the first sort box, and click LastName. Click Next. Click Next. Click the Enable Data Integrity check box. Click Finish. Click Yes.

Create a new form based on the List form application part.

On the Create tab, in the Templates group, click the APPLICATION PARTS button. In the Blank Forms selection, click LIST.

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.

Open the wizard to have Access analyze the Staff table. Accept the recommendations. Rename Table2 to Department. 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. Verify that the YES, LET 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.

Create a linked table to link to the WageIncreases table in the Staff database.

On the External Data tab, in the Import & Link group, click the NEW DATA SOURCE button 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.

Use the wizard to create a query to find employees who have received more than one wage increase. Find all the records with duplicate values in the StaffID field of the WageIncreases table. Include the IncreaseDate and IncreaseType fields in the query results. Accept the suggested name for the query and view the results when you are finished.

In the CreateRibbon Tab in the QueriesRibbon Group, you clicked the Query Wizardbutton. Inside the New Querydialog from the QueryTypelist, you selected Find Duplicates Query Wizard. Inside the NewQuerydialog, you clicked the OKbutton. Inside the Find Duplicates Query Wizarddialog from the Table/Query Selectlist, you selected Table: WageIncreases. Inside the Find Duplicates Query Wizarddialog, you clicked the Next >button. Inside the Find Duplicates Query Wizarddialog from the Available Fieldslist, you double-clicked the StaffIDlist item. Inside the Find Duplicates Query Wizarddialog, you clicked the Next >button. Inside the Find Duplicates Query Wizarddialog from the Additional Available Fieldslist, you double-clicked the IncreaseDatelist item, doublelicked the IncreaseTypelist item. Inside the Find Duplicates Query Wizarddialog, you clicked the Next >button, clicked the Finishbutton

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.

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.

From Design view, add the Insurance 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 UniverityLogo 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 Staff 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 Staff. Click Next. Click Next. Click Finish.

From Design view, add the Approval image file in an image control to the form Detail section just below the InsuranceType label.

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 the click the OK button.

Add a subform to the bottom of this form to display all the fields from the WageIncreases 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. Expand the Tables/Queries list and select Table: WageIncreases. Click the >> button. Click Next. Click Next. Click Finish.

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 the SCROLL BARS property box, expand the list, and select NEITHER.

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, int he Tools group, click the TAB ORDER button. Click the row selector next to FirstName. Hold down the mouse button and drag to between he StudentID and LastName rows. Release the mouse button. Click OK.

Change the query to an append query to copy records to the SecurityBackup 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.

Make this query an update query to increase values in the Premium field by 10%. Use the expression [Premium]*1.1. 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.

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 the same size as the tallest control selected.

On the Report Design Tools Arrange tab, in the Sizing and 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 WageIncreasesReport report. Use the StaffID field to link the report and subreport - the second suggested link in the list. Finish the subreport without changing the subreport name.

On the Report Design Tools Design tab, in the Controls group, click SUBFORM/SUBREPORT button. If the Controls gallery is not visible on the Ribbon, click the Controls button to display it. CLick on the Detail section below the ID Control . Click the USE AN EXISTING REPORT OR FROM 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 InsurancePlans 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 ID control. Click Next. Expand the Tables/Queries list, and select Table: Insurance Plans. Click the >> button. Click Next. Click Next. Click Finish.

From Design view, group the records in the report by values in the DepartmentName 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 DepartmentName.