Microsoft Access 2010

MicrosoftAccess 2010 Lab 10 Querying a Database http://www.sophea.info/portal/Teaching/DelawareValleyCollege/tabid/64/Default.aspx

Objectives • Understand query and general guidelines for creating query • Create queries using Query Design • Use text and numeric data in criteria • Create and use parameter queries • Create query with calculated field Querying a Database

What is query? • A request for information from a database • User retrieves information from a database using query • Querying is a process of converting data into information Querying a Database

Querying a Database Give me the number, name, amount paid, and current due for client BC76 List the client number, name, business analyst number, and amount paid for all clients. Sort the results by business analyst number and amount paid. Querying a Database

Two ways of creating query • User retrieves information from a database using • Query by example (QBE): use GUI to assist users with retrieving data • Query language: Access also uses Jet SQL, a version of Simple Query Language (SQL), English-like statements that allow users to create query. Querying a Database

Querying a Database

General Guidelines • Identify the fields needed for the query • Determine the table that contains the fields and whether more than one table is required • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Querying a Database

Practice Query 1 Querying a Database

1, 2. Identify the fields and table needed for the query • Read the query question and determine the fields and table required. Practice Query 1: Give me the number, name, amount paid, and current due of all clients. Sort the result by client name in ascending order. Querying a Database

3,4,5. The answers are… • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Practice Query 1: Give me the number, name, amount paid, and current due of all clients. Sort the result by client name in ascending order. Querying a Database

Creating a Query in Design View • Click Create on the Ribbon to display the Create tab • Click the Query Design button (Create tab | Queries group) to create a new query Querying a Database

Creating a Query in Design View • Click the table to add to the query • Click the Add button to add the selected table to the query • Click the Close button to remove the dialog box from the screen Querying a Database

Creating a Query inDesign View • Drag the lower edge of the field list down far enough so all fields in the table appear Querying a Database

Creating a Query in Design View • Double-click each field to add to the query • the client number, name, amount paid, and current due) Querying a Database

Viewing the result • Click View button and select Datasheet view (or Run) to display the result of the query, adjust the column width to show the result completely. Querying a Database

Sorting the results • Click the Sort row below the field you wish to sort (client name) • Click the Sort row arrow to display a menu of possible sort orders, select ascending from the list. Querying a Database

1, 2. Identify the fields and table needed for the query • Read the query question and determine the fields and table required. Practice Query 2: List the client number, name, amount paid, current due, contract hours YTD, and business analyst number for all clients whose business analyst number is 14. Querying a Database

3,4,5. The answers are… • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Practice Query 2: List the client number, name, amount paid, current due, contract hours YTD, and business analyst number for all clients whose business analyst number is 14. Querying a Database

Using a Number in a Criterion Querying a Database

Using a Comparison Operator in a Criterion Querying a Database

Using a Comparison Operator in a Criterion • Open the query in Design view • Enter the criterion with a comparison operator Querying a Database

Using a Compound Criterion Involving AND • Open the query in Design view • Add the criteria for two fields in the Criteria row Querying a Database

Using a Compound Criterion Involving OR • Open the query in Design view • Add criterion for one field to the Criteria row • Add criterion for another field in the or row (the row below the Criteria row) Querying a Database

Using Text Data in Criterion • Click the Criteria row for the field to produce an insertion point • Type the criterion • Click the View button to display the query results Querying a Database

Using Criteria for a Field Not Included in the Results • With the desired query open, click the Show check box to remove the check mark for a field containing criteria Querying a Database

Using Criteria for a Field Not Included in the Results Querying a Database

Using a Wildcard in Criterion • The asterisk wildcard character (*) matches any character or any block of characters in a specific position. • E.g. List all customer whose name start with Gr • Gr* will return both Grant and Grumpy • The question mark character (?) serves as a single-character placeholder. • E.g. m?ggie will return both maggie and meggie Querying a Database

Using a Wildcard Querying a Database

1, 2. Identify the fields and table needed for the query • Read the query question and determine the fields and table required. Practice Query 3: List the client number, name, amount paid, current due, contract hours YTD, and business analyst number for all clients whose state can be searched by the query user. Querying a Database

3,4,5. The answers are… • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Practice Query 3: List the client number, name, amount paid, current due, contract hours YTD, and business analyst number for all clients whose state can be searched by the query user.. Querying a Database

Creating and Viewing a Parameter Query • If necessary, return to Design view and type the criterion for a parameter query ([Enter City], for example) • Run the query Querying a Database

Creating and Viewing a Parameter Query Querying a Database

1, 2. Identify the fields and table needed for the query • Read the query question and determine the fields and table required. Practice Query 4: List the client number, name, amount paid, current due, and the total amount (amount paid plus current due) for each client. Querying a Database

3,4,5. The answers are… • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Practice Query 4: List the client number, name, amount paid, current due, and the total amount (amount paid plus current due) for each client. Querying a Database

Using a Calculated Field in a Query • Open the query in Design view containing a field that can be calculated • Right-click the Field row in the first open column in the design grid to display a shortcut menu • Click Zoom on the shortcut menu to display the Zoom dialog box • Type the calculation in the Zoom dialog box • Click the OK button (Zoom dialog box) to enter the expression Querying a Database

Using a Calculated Field in a Query Querying a Database

Clearing the Design Grid • Open the query in Design view • Click just above the column heading in the first column in the grid to select the column • Hold the SHIFT key down and click just above the last column heading to select all the columns • Press the DELETE key to clear the design grid Querying a Database

Clearing the Design Grid Querying a Database

Microsoft Access 2010