new perspectives: access.05
DESCRIPTION
TRANSCRIPT
![Page 1: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/1.jpg)
®
Microsoft Access 2010
Tutorial 5
Creating Advanced Queries and Enhancing Table Design
![Page 2: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/2.jpg)
XPXPXPObjectives• Review table and object naming standards• Use the Like, In, Not, and & operators in
queries• Filter data using an AutoFilter• Use the IIf function to assign a conditional
value to a calculated field in a query• Create a parameter query
New Perspectives on Microsoft Access 2010 2
![Page 3: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/3.jpg)
XPXPXPObjectives• Use query wizards to create a crosstab query, a
find duplicates query, and a find unmatched query
• Create a top values query• Modify table designs using lookup fields, input
masks, and data validation rules• Identify object dependencies• Review a Memo field’s properties• Designate a trusted folder
New Perspectives on Microsoft Access 2010 3
![Page 4: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/4.jpg)
XPXPXPDatabase Naming Conventions
New Perspectives on Microsoft Access 2010 4
![Page 5: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/5.jpg)
XPXPXPUsing a Pattern Match in a Query• A pattern match selects records with a value for
the designated field that matches the pattern of the simple condition value
• The Like comparison operator selects records by matching field values to a specific pattern that includes one or more of these wildcard characters: asterisk (*), question mark (?), and number symbol (#)
New Perspectives on Microsoft Access 2010 5
![Page 6: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/6.jpg)
XPXPXPUsing a List-of-Values Match in a Query• A list-of-values match selects records whose
value for the designated field matches one of two or more simple condition values
• The In comparison operator lets you define a condition with a list of two or more values for a field
New Perspectives on Microsoft Access 2010 6
![Page 7: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/7.jpg)
XPXPXPUsing the Not Logical Operator in a Query• The Not logical operator negates a criterion or
selects records for which the designated field does not match the criterion
New Perspectives on Microsoft Access 2010 7
![Page 8: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/8.jpg)
XPXPXPUsing an AutoFilter to Filter Data
New Perspectives on Microsoft Access 2010 8
![Page 9: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/9.jpg)
XPXPXPAssigning a Conditional Value to a Calculated Field• The & (ampersand) operator is a concatenation
operator that joins text expressions• The IIf (Immediate If) function assigns one value to a
calculated field or control if a condition is true, and a second value if the condition is false
• The IsNull function tests a field value or an expression for a null value; if the field value or expression is null, the result is true; otherwise, the result is false
New Perspectives on Microsoft Access 2010 9
![Page 10: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/10.jpg)
XPXPXPCreating a Parameter Query• A parameter query displays a dialog box that prompts
the user to enter one or more criteria values when the query is run
• Create a select query that includes all fields to appear in the query results. Also choose the sort fields and set the criteria that do not change when you run the query
• Decide which fields to use as prompts when the query runs. In the Criteria box for each of these fields, type the prompt you want to appear in a dialog box when you run the query, and enclose the prompt in brackets
New Perspectives on Microsoft Access 2010 10
![Page 11: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/11.jpg)
XPXPXPCreating a Parameter Query
New Perspectives on Microsoft Access 2010 11
![Page 12: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/12.jpg)
XPXPXPCreating a Crosstab Query
New Perspectives on Microsoft Access 2010 12
![Page 13: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/13.jpg)
XPXPXPCreating a Crosstab Query• A crosstab query uses aggregate functions to
perform arithmetic operations on selected records
New Perspectives on Microsoft Access 2010 13
![Page 14: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/14.jpg)
XPXPXPCreating a Crosstab Query• In the Queries group on the Create tab, click the
Query Wizard button• In the New Query dialog box, click Crosstab Query
Wizard, and then click the OK button• Complete the Wizard dialog boxes to select the table
or query on which to base the crosstab query, select the row heading field (or fields), select the column heading field, select the calculation field and its aggregate function, and enter a name for the crosstab query
New Perspectives on Microsoft Access 2010 14
![Page 15: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/15.jpg)
XPXPXPCreating a Crosstab Query
New Perspectives on Microsoft Access 2010 15
![Page 16: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/16.jpg)
XPXPXPRunning a Crosstab Query
New Perspectives on Microsoft Access 2010 16
![Page 17: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/17.jpg)
XPXPXPCreating a Find Duplicates Query• A find duplicates query is a select query that finds
duplicate records in a table or query• In the Queries group on the Create tab, click the
Query Wizard button• Click Find Duplicates Query Wizard, and then click the
OK button• Complete the Wizard dialog boxes to select the table
or query on which to base the query, select the field (or fields) to check for duplicate values, select the additional fields to include in the query results, enter a name for the query, and then click the Finish button
New Perspectives on Microsoft Access 2010 17
![Page 18: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/18.jpg)
XPXPXPCreating a Find Unmatched Query• A find unmatched query is a select query that finds all records
in a table or query that have no related records in a second table or query
• In the Queries group on the Create tab, click the Query Wizard button
• Click Find Unmatched Query Wizard, and then click the OK button
• Complete the Wizard dialog boxes to select the table or query on which to base the new query, select the table or query that contains the related records, specify the common field in each table or query, select the additional fields to include in the query results, enter a name for the query, and then click the Finish button
New Perspectives on Microsoft Access 2010 18
![Page 19: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/19.jpg)
XPXPXPCreating a Find Unmatched Query
New Perspectives on Microsoft Access 2010 19
![Page 20: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/20.jpg)
XPXPXPCreating a Top Values Query• The Top Values property for a query lets you
limit the number of records in the query results
• Create a select query with the necessary fields and sorting and selection criteria
• In the Query Setup group on the Design tab, enter the number of records (or percentage of records) you want selected in the Return (Top Values) text box
New Perspectives on Microsoft Access 2010 20
![Page 21: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/21.jpg)
XPXPXPCreating a Top Values Query
New Perspectives on Microsoft Access 2010 21
![Page 22: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/22.jpg)
XPXPXPLookup Fields and Input Masks
New Perspectives on Microsoft Access 2010 22
![Page 23: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/23.jpg)
XPXPXPCreating a Lookup Field• A lookup field lets the user select a value from
a list of possible values• You use a Lookup Wizard field in Access to
create a lookup field in a table
New Perspectives on Microsoft Access 2010 23
![Page 24: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/24.jpg)
XPXPXPCreating a Lookup Field
New Perspectives on Microsoft Access 2010 24
![Page 25: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/25.jpg)
XPXPXPUsing a Lookup Field
New Perspectives on Microsoft Access 2010 25
![Page 26: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/26.jpg)
XPXPXPUsing the Input Mask Wizard• A literal display character is a special character
that automatically appears in specific positions of a field value; users don’t need to type literal display characters– Input mask
New Perspectives on Microsoft Access 2010 26
![Page 27: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/27.jpg)
XPXPXPUsing the Input Mask Wizard
New Perspectives on Microsoft Access 2010 27
![Page 28: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/28.jpg)
XPXPXPUsing the Input Mask Wizard
New Perspectives on Microsoft Access 2010 28
![Page 29: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/29.jpg)
XPXPXPIdentifying Object Dependencies• An object dependency exists between two
objects when a change to the properties of data in one object affects the properties of data in the other object
• The Object Dependencies pane displays a collapsible list of the dependencies among the objects in an Access database; you click the list’s expand indicators to show or hide different levels of dependencies
New Perspectives on Microsoft Access 2010 29
![Page 30: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/30.jpg)
XPXPXPIdentifying Object Dependencies
New Perspectives on Microsoft Access 2010 30
![Page 31: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/31.jpg)
XPXPXPDefining Data Validation Rules• To prevent a user from entering an incorrect value in
the Zip field, you can create a field validation rule• The Validation Rule property value specifies the valid
values that users can enter in a field• The Validation Text property value will be displayed
in a dialog box if the user enters an invalid value• A table validation rule compares one field value in a
table record to another field value in the same record to verify their relative accuracy
New Perspectives on Microsoft Access 2010 31
![Page 32: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/32.jpg)
XPXPXPDefining Data Validation Rules
New Perspectives on Microsoft Access 2010 32
![Page 33: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/33.jpg)
XPXPXPWorking with Memo Fields• You use a Memo field for long comments and
explanations• Text fields are limited to 255 characters, but
Memo fields can hold up to 65,535 characters
New Perspectives on Microsoft Access 2010 33
![Page 34: New Perspectives: Access.05](https://reader035.vdocuments.us/reader035/viewer/2022081422/553ab3654a79593e298b45b7/html5/thumbnails/34.jpg)
XPXPXPDesignating a Trusted Folder• A trusted folder is a folder on a drive or network
that you designate as trusted and where you place databases you know are safe
New Perspectives on Microsoft Access 2010 34