WORKING WITH QUERIES In this section you ll learn

WORKING WITH QUERIES In this section, you'll learn how to: • Create queries • Sort results • Add criteria • Employ Boolean operators

CREATE QUERIES • Start Base, and open the Flower. Store database. • In the Database list, click Queries. • Click Create Query in Design View. • The Query Design window should appear. • In the Add Tables window, click Customers. • Then click the ADD button. • Click the Close button.

ADD FIELDS TO QUERY • Click the arrow in the first column's Field box. • When the list appears, click Customers. Customer ID. • Drag the First Name field from the Customers table to the second column of the query design grid. • Release the mouse button. • Drag the Last Name field to the design grid.

Run the query • The query is now set up to show the Customer ID, First Name, and Last Name of each customer in the Customers table. • On the Toolbar, click the Run Query icon. • The query shows the Customer ID, First Name, and Last Name of each customer in the Customers table.

Sort results • In the Last Name field, click in the Sort row. When the drop-down arrow appears, click it, then click Ascending. • On the Toolbar, click the Run Query icon. The query should run, and sort the records alphabetically by Last Name: • In the Last Name field, click in the Sort row, then click (not sorted).

Add criteria • In the query's Customer table, scroll down to the State field. Add the State field to the query design grid. • In the State field, click in the Criterion row. Type: VA. Press the ENTER key. TIP: Notice that Base automatically surrounded the VA criterion with quotes. That's because the State field stores text data. In database query language, quotes signify a string of text. • On the Toolbar, click the Run Query icon. It has returned all customers who live in the VA State.

Employ Boolean operators • What are Boolean operators? • Boolean operators are expressions such as AND, OR, NEITHER, and NOR that allow you to add multiple criteria to a query. They take their name from George Boole, the mathematician who first used them.

• If you had a T-shirt store with an Base database, for instance, and you wanted to find out how many of your California customers had ordered blue T-shirts, you'd employ the AND operator in your query: customers from California AND who also bought blue T-shirts

• If you wanted to see how many customers were from California (these California customers could have bought T-shirts of any color), and how many customers bought blue T -shirts (these blue T-shirt customers could be from anywhere), you'd employ the OR operator: • customers from California • OR • customers who bought blue T-shirts

Employ the OR operator • In the State field, click in the Or row under the criterion VA. • Type: NY then press the ENTER key. • Click the Run Query icon.

Employ the AND operator • Remove the query criteria from the State field. TIP: Highlight the contents of the State field, then press the DELETE key. • In the First Name field, click in the Criterion row. Type: Jill then press the ENTER key. In the State field, type: NY in the Criterion row then press the ENTER key. • Click the Run Query icon. No records were returned, because there's no one in the table whose first name is Jill AND lives in New York.

Employ a Wild Card character • Remove all query criteria from all fields. The design grid should be blank. • Add the Company Name field to the query: Drag it from the field list in the Customers table and drop it in the blank field to the right of the State field.

• In the Company Name field, click in the Criterion row. Type: LIKE A* then press the ENTER key. • TIP: An asterisk (*) stands for any character or combination of characters. For instance, Ap* would match Ape, Aptitude, Apparent, etc. The LIKE keyword is used to search for a pattern. • Click the Run Query icon. • The query shows all companies whose names begin with A. • On the Menu Bar, click File, then Save.

• When the Save As window appears, type: Companies that begin with A in the Query Name box. • Click the OK button. • On the Menu Bar, click File, then Close. • You should return to the Flower. Store database window. • Exit the database and close Base.

Practice: Working with Queries • Start Base. Open the Library 2 database. • Create a new query in Design view based on the Authors table. Add all the fields to the design grid. • Sort Authors Names in Ascending order. • Add the criteria Yes to the Good Author field. Run the query. • Close the query, saving it as Good Authors. • Exit Base.

http: //inpics. net/tutorials/base 2/queri es 8. html