Microsoft Office Access 2007: Level 2
Microsoft® Office Access™ 2007: Level 2
Lesson 1: Controlling Data Entry
Topic 1-A: Restrict Data Entry Using Field Properties
How to Restrict Data Entry Using Field Properties
1-1 / Multiple Choice( Q ) / Which property is used to automatically enter a value for a field when you create a new record?
A . / Caption
B . / Default Value
C . / Validation Rule
D . / Validation Text
1-2 / Performance Based
( Q ) / Set the default value of the Hours field to 45, save the table as tblMyPayroll and observe the result in a new record.
Topic 1-B: Establish a Pattern for Entering Field Values
How to Establish a Pattern for Entering Field Values
1-1 / Multiple Choice( Q ) / Which option is not a predefined input mask in the Input Mask wizard?
A . / Phone Number
B . / Zip Code
C . / Social Security number
D . / Employee ID
1-2 / Performance Based
( Q ) / Create a short date input mask for the HireDate field, save the table as tblMyPersonal and check the pattern by entering 04/21/1979 in that field.
Topic 1-C: Create a List of Values for a Field
How to Create a List of Values for a Field
1-1 / Multiple Choice( Q ) / How can you create a Lookup List field?
A . / In the Design view, on the Design tab, in the Tools group, click Builder; follow the steps in the wizard and click Finish.
B . / In the Design view, select Lookup Wizard from the Data Type drop-down list; follow the steps in the wizard and click Finish.
C . / In the Design view, select Lookup Wizard from the Field Name drop-down list; follow the steps in the wizard and click Finish.
D . / In the Design view, select Lookup Wizard from the Field Name drop-down list; follow the steps in the wizard and click Cancel.
1-2 / True/False
( Q ) / True or False? The Column Heads property includes the column headings in the Lookup list.
A . / True
B . / False
Lesson 2: Joining Tables
Topic 2-A: Create Query Joins
How to Create Query Joins
2-1 / Multiple Choice( Q ) / Which type of Join is also known as equi-join?
A . / Query Join
B . / Inner Join
C . / Left Outer Join
D . / Right Outer Join
2-2 / Performance Based
( Q ) / Create a Right Outer Join between the Part# field in tblBookProject table and the PartNumber field in tblBookSales table and then, run the query.
Topic 2-B: Join Unrelated Tables
How to Join Unrelated Tables
2-1 / Multiple Choice( Q ) / How can you join two unrelated tables?
A . / In the Query Design view, on the Design tab, in the Query Setup group, click Show Table and add a table that contains fields related to both tables.
B . / In the Design view, on the Design tab, in the Query Setup group, click Show Table and add a table that contains fields related to one of tables.
C . / In the Query Design view, on the Design tab, in the Query Type group, click Show Table and add a table that contains fields related to both tables.
D . / In the Design view, on the Create tab, in the Query Setup group, click Show Table and add a table that contains fields related to one of the tables.
2-2 / True/False
( Q ) / True or False? The Show Table window is used to add only tables.
A . / True
B . / False
Topic 2-C: Relate Data Within a Table
How to Relate Data Within a Table
2-1 / Multiple Choice( Q ) / Which property can you use to change the name of one of the tables in a Self Join?
A . / Description
B . / Caption
C . / Alias
D . / Source
2-2 / Performance Based
( Q ) / You want to relate data within the tblBookProject table. For this purpose, you want to add a copy of the table to the query design and name it Books.
Lesson 3: Creating Flexible Queries
Topic 3-A: Set Select Query Properties
How to Set Select Query Properties
3-1 / Multiple Choice( Q ) / How will you restrict the records returned by a query to those that have unique values in all fields in the underlying tables or queries?
A . / Set the Unique Values select query property to Yes.
B . / Set the Unique Records select query property to Yes.
C . / Set the Unique Records select query property to No.
D . / Set the Unique Values select query property to No.
3-2 / Multiple Choice
( Q ) / How will you use properties to restrict a query to display only the top 5 percent of the records returned?
A . / In the query DataSheet view, set the Top Values property to 5%.
B . / In the query DataSheet view, set the Top Values property to 5.
C . / In the query Design view, set the Top Values property to 5%.
D . / In the query Design view, set the Top Values property to 5.
Topic 3-B: Create Parameter Queries
How to Create Parameter Queries
3-1 / Multiple Choice( Q ) / To create a single-parameter query that requests a criterion from the user, what do you need to type in the Criteria cell for the appropriate field?
A . / The desired prompt message followed by the asterisk wildcard.
B . / The Criteria value for the query.
C . / The desired prompt message within square brackets.
D . / The Like keyword followed by the desired prompt message.
3-2 / Performance Based
( Q ) / Create a query that displays the Title, BookPrice, and Quantity fields in that order, where the BookPrice value ranges between 25 and 40, and then run the query.
Topic 3-C: Create Action Queries
How to Create Action Queries
3-1 / Multiple Choice( Q ) / You need to create an action query that will update the price data in a Products table by an increase of 5 percent for products in the PRD-1 category. You have already created and tested a select query that displays the desired records to be updated. What will be your next step?
A . / In the Datasheet view, in the Query Type group, select the Update option.
B . / In the Design view, in the Query Type group, select the Update option.
C . / In the Datasheet view, in the Update To cell in the field grid, type the appropriate command to increase the price by 5 percent.
D . / In the Design view, in the Update To cell in the field grid, type the appropriate command to increase the price by 5 percent.
3-2 / Performance Based
( Q ) / Create a Make Table query to copy all the fields of the tblBooks table to a new table tblBooksUpdated and then run the query.
Lesson 4: Improving Forms
Topic 4-A: Design a Form Layout
How to Design a Form Layout
4-1 / Multiple Choice( Q ) / Which group on the Design contextual tab has commands to edit the properties of a form?
A . / Tools
B . / Controls
C . / Gridlines
D . / Font
4-2 / Performance Based
( Q ) / On a form in the Design view, set the gridlines to Outline, width to 2 pt, and Anchoring to Stretch Down.
Topic 4-B: Enhance the Appearance of a Form
How to Enhance the Appearance of a Form
4-1 / Multiple Choice( Q ) / From which group can you launch the Rectangle tool?
A . / Controls
B . / Gridlines
C . / Font
D . / Tools
4-2 / Performance Based
( Q ) / Apply bold and underline formatting to the PayRate field when the field value is between 20 and 40.
Topic 4-C: Restrict Data Entry in Forms
How to Restrict Data Entry in Forms
4-1 / Multiple Choice( Q ) / How do you restrict data entry in forms using a Combo Box control?
A . / Select the Use Control Wizards option. Then, select the Combo Box (Form Control) tool, click in the form to place the control in the desired location, select the appropriate options in the wizard, and click Finish.
B . / Select the Toggle Button option. Then, select the Combo Box (Form Control) tool, click in the form to place the control in the desired location, select the appropriate options in the wizard, and click Finish.
C . / Select the Select option. Then, select the Combo Box (Form Control) tool, click in the form to place the control in the desired location, select the appropriate options in the wizard, and click Finish.
D . / Select the Set Control Defaults option. Then, select the Combo Box (Form Control) tool, click in the form to place the control in the desired location, select the appropriate options in the wizard, and click Finish.
4-2 / True/False
( Q ) / True or False? In order to start the Option Group wizard, verify that the Use Control Wizards tool is selected, select the Option Group tool, and click the form in the approximate area where you want the option group to be displayed.
A . / True
B . / False
Topic 4-D: Add a Command Button to a Form
How to Add a Command Button to a Form
4-1 / Multiple Choice( Q ) / Which tool should be enabled in order to launch the Command Button wizard?
A . / Use Control Wizards
B . / Select
C . / Attachment
D . / Toggle Button
4-2 / Performance Based
( Q ) / Create a command button with the name Quit Application and the label Quit to quit the application.
Topic 4-E: Create a Subform
How to Create a Subform
4-1 / Multiple Choice( Q ) / How will you create a subform?
A . / In the Controls group, select the Subform/Subreport tool, click in the location of the form in which you'd like to place the subform, follow the steps in the wizard, and click Finish.
B . / In the Controls group, select the Attachment tool, click in the location of the form in which you'd like to place the subform attachment, follow the steps in the wizard, and click Finish.
C . / In the Tools group, select the Subform/Subreport tool, click in the location of the form in which you'd like to place the subform, follow the steps in the wizard, and click Finish.
D . / In the Controls group, select the Subform In New Window tool, click in the location of the form in which you'd like to place the subform, follow the steps in the wizard, and click Finish.
4-2 / True/False
( Q ) / True or False? When using the Subform/Subreport wizard to add a subform to a main form, you can base the subform on an appropriate table, query, or preexisting form.
A . / True
B . / False
Lesson 5: Customizing Reports
Topic 5-A: Organize Report Information
How to Organize Report Information
5-1 / Multiple Choice( Q ) / Which pane would you use to organize report information?
A . / The Field List pane
B . / The Group, Sort And Total pane
C . / The Navigation Pane
D . / The Property Sheet pane
5-2 / Performance Based
( Q ) / Sort the records by CustomerName and then close the Group, Sort, And Total pane.
Topic 5-B: Format the Report
How to Format a Report
5-1 / Multiple Choice( Q ) / Which group allows you to resize a set of selected controls uniformly?
A . / AutoFormat
B . / Size
C . / Control Alignment
D . / Position
5-2 / Performance Based
( Q ) / Format the selected title by setting Algerian as the font and red as the font color.
Topic 5-C: Set Report Control Properties
How to Set Report Control Properties
5-1 / Multiple Choice( Q ) / Which control property is used to display the complete text of a title?
A . / Text Align
B . / Width
C . / Can Grow
D . / Caption
5-2 / Performance Based
( Q ) / Hide the repeating data in the CustomerID column.
Topic 5-D: Control Report Pagination
How to Control Report Pagination
5-1 / Multiple Choice( Q ) / How can you force each new section to print on a new page of the report.
A . / Use the Force New Page property where appropriate.
B . / Use the Keep Together property where appropriate.
C . / Remove any company logos or other graphics from your report.
D . / Drag the section header to the desired location on the vertical ruler.
5-2 / Performance Based
( Q ) / Set the keep together property for the rptSalesAndProfit report.
Topic 5-E: Summarize Report Information
How to Summarize Report Information
5-1 / Multiple Choice( Q ) / How will you summarize report information?
A . / Select the desired column in the report and in the Controls group, click Totals and select an aggregate function.
B . / Select the desired column in the report and in the Formatting group, click Group & Sort and select an aggregate function.
C . / Select the desired column in the report and in the Grouping & Totals group, click Group & Sort and select an aggregate function.
D . / Select the desired column in the report and in the Grouping & Totals group, click Totals and select an aggregate function.
5-2 / Multiple Choice
( Q ) / Where does the summary field appear in the report?
A . / In the Report Header section
B . / In the Report Footer section
C . / In the Detail section
D . / In the Group Header section
Topic 5-F: Add a Subreport to an Existing Report
How to Add a Subreport to an Existing Report
5-1 / Multiple Choice( Q ) / How will you add a subreport to the footer of an existing report?
A . / In the Layout view, in the Controls group, select the Subform/Subreport tool, click in the report footer section, and then follow the steps in the wizard.
B . / In the Design view, in the Controls group, select the Subform/Subreport tool, click in the report footer section, and then follow the steps in the wizard.
C . / In the Design view, in the Tools group, select the Subform/Subreport tool, click in the report footer section, and then follow the steps in the wizard.
D . / In the Layout view, in the Tools group, select the Subform/Subreport tool, click in the report footer section, and then follow the steps in the wizard.
5-2 / True/False
( Q ) / True or False? To create a subreport using the Subreport Wizard, you need to provide the record source for the subreport, the fields that will link the main report to the subreport, and a name for the subreport.
A . / True
B . / False
Topic 5-G: Create a Mailing Label Report
How to Create a Mailing Label Report
5-1 / Multiple Choice( Q ) / How will you create a mailing label report?
A . / On the Create tab, in the Reports group, click Labels, follow the steps in the wizard, and click Finish.
B . / On the Create tab, in the Reports group, click Report Wizard, follow the steps in the wizard, and click Finish.
C . / On the Create tab, in the Reports group, click Report, select the fields using which you would be creating a label, and click Save.
D . / On the Create tab, in the Reports group, click Report Design and design the label in the Design view.
5-2 / Multiple Response
( Q ) / What aspects of mailing labels can you set by using the Label Wizard?
A . / The table or query that will provide the data for the labels
B . / The type of mailing label to be used
C . / The text style of the labels
D . / A background style for the labels
Lesson 6: Sharing Data Across Applications
Topic 6-A: Import Data into Access
How to Import Data into Access
6-1 / Multiple Choice( Q ) / Which command in the Import group on the External Data tab helps you link data from one database to another?
A . / Access
B . / Excel
C . / ODBC Database
D . / dBase File
6-2 / Performance Based
( Q ) / Import the tblComputers.xlsx file located in the C drive into the Access database by setting EmployeeID as the primary key, applying column heading format to the first row, and accepting the other defaults.
Topic 6-B: Export Data
How to Export Data
6-1 / Multiple Choice( Q ) / Which option will you use to export a record to a Word document?
A . / In the Import group, click Text File.
B . / In the Export group, click Text File.
C . / In the Export group, click Word.
D . / In the Import group, click Word.
6-2 / Performance Based
( Q ) / Export the tblBookSales as a Word document and save it in the C drive without saving the export steps. While performing the export steps, check the Open Destination File After the Operation Is Complete check box to open the exported file.
Topic 6-C: Analyze Access Data in Excel
How to Analyze Access Data in Excel
6-1 / Multiple Choice( Q ) / Which statement is true about analyzing Access data in Excel?
A . / You can analyze the data in an Excel spreadsheet using the options in Excel and then import it into the database.
B . / You can link the Excel spreadsheet into the database and analyze its data using the options in Excel.
C . / You can export the required Access database object as an Excel spreadsheet and analyze its data using charts and Illustrations.
D . / You can analyze the data in an Excel spreadsheet and then append the data to a table in the database.
6-2 / Multiple Response
( Q ) / You have some data in an Access database that you want to analyze in Excel. Which database objects would you be able to use to accomplish this task?
A . / Query
B . / Form
C . / Report
D . / Table
Topic 6-D: Export Data to a Text File
How to Export Query Data to a Text File
6-1 / Multiple Choice( Q ) / How would you export Access data to a Text file?
A . / In the Export group, click Word, click OK, follow the steps in the wizard, and click Finish.
B . / In the Import group, click Text File, click OK, follow the steps in the wizard, and click Finish.
C . / In the Export group, click Text File, click OK, follow the steps in the wizard, and click Finish.
D . / In the Export group, click File, click OK, follow the steps in the wizard, and close the wizard.
6-2 / True/False
( Q ) / True or False? When exporting a table to a comma-delimited text file, you need to set the Save As file type and the export format before choosing the comma character as the delimiter.
A . / True
B . / False
Topic 6-E: Merge Access Data with a Word Document