tutorial 5 creating advanced queries and enhancing table design
Download
Skip this Video
Download Presentation
Tutorial 5 Creating Advanced Queries and Enhancing Table Design

Loading in 2 Seconds...

play fullscreen
1 / 38

Tutorial 5 Creating Advanced Queries and Enhancing Table Design - PowerPoint PPT Presentation


  • 114 Views
  • Uploaded on

Tutorial 5 Creating Advanced Queries and Enhancing Table Design. Objectives. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Tutorial 5 Creating Advanced Queries and Enhancing Table Design' - rivka


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
objectives
Objectives
  • 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

objectives1
Objectives
  • 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

database naming conventions
Database Naming Conventions

New Perspectives on Microsoft Access 2010

using a pattern match in a query
Using 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

using a list of values match in a query
Using 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

using the not logical operator in a query
Using 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

using an autofilter to filter data
Using an AutoFilter to Filter Data

New Perspectives on Microsoft Access 2010

assigning a conditional value to a calculated field
Assigning 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

creating a parameter query
Creating 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

creating a parameter query1
Creating a Parameter Query

New Perspectives on Microsoft Access 2010

creating a crosstab query
Creating a Crosstab Query

New Perspectives on Microsoft Access 2010

creating a crosstab query1
Creating a Crosstab Query
  • A crosstab query uses aggregate functions to perform arithmetic operations on selected records

New Perspectives on Microsoft Access 2010

creating a crosstab query2
Creating 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

creating a crosstab query3
Creating a Crosstab Query

New Perspectives on Microsoft Access 2010

running a crosstab query
Running a Crosstab Query

New Perspectives on Microsoft Access 2010

creating a find duplicates query
Creating 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

creating a find unmatched query
Creating 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

creating a find unmatched query1
Creating a Find Unmatched Query

New Perspectives on Microsoft Access 2010

creating a top values query
Creating 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

creating a top values query1
Creating a Top Values Query

New Perspectives on Microsoft Access 2010

lookup fields and input masks
Lookup Fields and Input Masks

New Perspectives on Microsoft Access 2010

creating a lookup field
Creating 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

creating a lookup field1
Creating a Lookup Field

New Perspectives on Microsoft Access 2010

using a lookup field
Using a Lookup Field

New Perspectives on Microsoft Access 2010

using the input mask wizard
Using 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

using the input mask wizard1
Using the Input Mask Wizard

New Perspectives on Microsoft Access 2010

using the input mask wizard2
Using the Input Mask Wizard

New Perspectives on Microsoft Access 2010

identifying object dependencies
Identifying 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

identifying object dependencies1
Identifying Object Dependencies

New Perspectives on Microsoft Access 2010

defining data validation rules
Defining 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

defining data validation rules1
Defining Data Validation Rules

New Perspectives on Microsoft Access 2010

working with memo fields
Working 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

designating a trusted folder
Designating 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

helpful hints
Helpful Hints
  • Review the criteria in the query below
  • Note that the “T” must be repeated on each line if the criteria is to display all Tuesday classes for both the Des Plaines campus and the Ray Hartstein campus. If the “T” is omitted on the RHC row, then all classes at the RHC campus, regardless of the day, will be displayed.

New Perspectives on Microsoft Access 2010

helpful hints1
Helpful Hints
  • Top Values query requires two tasks
    • Modify the ALL to either a number of records or a percentage of records
    • Sort the data in ascending or descending order on the field to display the highest (top) values or lowest (bottom) values
    • Both of the above tasks must be done; the sequence of events does not matter

New Perspectives on Microsoft Access 2010

additional examples
Additional Examples
  • Here are the basics for the IIF: It consists of three parts: the condition, what to do if the condition is true, what to do if the condition is false.
  • IIF(condition, True, False)
  • Examples:
    • IIF(BalanceDue>5000, "Call Customer", "Ignore")
    • IIF(Exam3>=70%, "Pass", "Redo")
    • IIF(Exam3>=90%, TotalPoints+25)
      • Adds 25 points to the TotalPoints if earned a 90% or higher on Exam3; otherwise, the TotalPoints stays as is
    • IIF(GPA>=3.5, "Honors")

New Perspectives on Microsoft Access 2010

additional examples1
Additional Examples
  • IIF(StateCode=“IN”,0%,9%)
    • Explanation: If the State Code is Indiana (IN), then there is no tax added; however, if the state code is any other value, the tax is 9%
  • IIF(ExamDate=#7/4/2010#, "Holiday", "Test Day")
  • IIF(WonAward=Yes, "Buy it", "Don't Buy it")
    • Datatype for WonAward is Yes/No not text
  • IIF(IsNull(Company), LastName, CompanyName)
    • Note the format; IsNull is a function and the syntax is different than the previous examples (no comparison operator is used (=, >, <>)

New Perspectives on Microsoft Access 2010

ad