141. Find Data in a Database  BEFORE YOU BEGIN 135 Create a Database
One of the best things about a database is that it keeps your information organized. Whenever you need to, you can quickly locate the exact data you need. One way to find information in a database is to create a query . A query is like a question that is posed to the databasewhat you get for an answer is a simple list of records that match your query criteria. Because a query is a question and not an actual report, you can run the query again and again as your data changes, and get results based on current information. A query can search the data in as many tables as you like, and combine that information in various ways for presentation, as you'll see in this task.  KEY TERM Query A "question" asked of the current data in a database. The result is a report of matching records.
| 1. | Click Queries
Click the Queries button in the Database pane of the Base window.
| | 2. | Click Use Wizard to Create Query Click the Use Wizard to Create Query link in the Tasks list, shown on the right side of the Base window. The Query Wizard appears. 141. Find Data in a Database   | | 3. | Select Fields In Step 1 of the Query Wizard, select the first table whose fields you want to search (or view in the resulting report) from the Tables list. The fields in that table appear in the Available fields list. For the query I'm creating, I want to list all the drivers on the Andretti Green and Rahal Letterman teams , so I'll pull information from the Drivers table. Select a field from the Available fields list that you want to search through or display on the resulting report and click > to add the field to the Fields in the Query list. Repeat to add more fields from the selected table to the query list. Change to another table if desired and add more fields if you like. I wanted to show information on each driver's car, so I pulled additional data from the Cars table. Click Next . TIP To use all the fields from the selected table on the query report, click >> instead. | | 4. | Select Sort
On Step 2 of the Query Wizard , choose a field by which to sort the resulting report from the Sort by list, and then choose Ascending or Descending to specify the sort order.
Add additional sorting levels by selecting a field from the Then by list, and choosing the sort order Ascending or Descending . You can add as many as four sort levels. Click Next.
| | 5. | Set Criteria In Step 3 of the Query Wizard , set limits on which records you want in the resulting report. Select a field to search from the Fields list, select a Condition , and then enter a Value . For example, I want to show only drivers on the Andretti Green and Rahal Letterman teams, so I started by selecting TeamName (from the Drivers table), and then set the other options to search for records where TeamName is equal to Andretti Green Racing. To find a field that's empty, look for one that's NULL . If you choose the "like" condition, you can match part of the text using wildcards. For example, I could have used this condition: TeamName like Rahal* , which searches for "Rahal" at the beginning of the TeamName field, followed by any number of characters . Likewise, I could have searched for Ra?al , where Rahal, Rajal , and Rabal are all matches. Because I also want to list the members of the Rahal Letterman team, I selected the Match any of the following option, and then set the second condition to search for records with Rahal Letterman in the TeamName field. You can set a third search criteria if you like. Click Next . NOTES Unless you have a value field (a field in a table with a number value), you'll automatically skip Step 4 , where you can choose to display all records, or just a summary (such as a total of all the sales from the North region). Step 5 Grouping and Step 6 Grouping conditions, when available, allow you to group similar records in the report. You can change the labels for each field in the query report in Step 7 by creating "aliases," but this step is optional and often not needed, especially if you use understandable field names when you create your tables. | | 6. | Save the Query
Because a query is a question you can ask over and over, using the current data in a database, you'll want to save your query. For example, because new drivers can be added to a team just for the Indy 500, I can run my query against the database and get an updated report on the current drivers for the Andretti Green and Rahal Letterman teams. In Step 8 of the Query Wizard , type a name for the query in the Name of the query box.
You can display the resulting report immediately by selecting the Display Query option, but typically you'll want to modify the query first, especially if you're combining data from multiple tables like I am. Choose Modify Query and click Finish. The query is saved and opened for editing.
| | 7. | Modify and Run Query
The tables you selected for the query appear in the middle of the window. (The result of a final query, shown here, appears above the tables.) Below the tables is a pane in which you can control the Sort , the searching Criterion , and the fields that appear on the report (they have a check mark in the Visible box.
If you're pulling data from multiple tables, you'll want to link the fields from each table that match (contain the same data). Being able to link related tables is what makes Base a relational database program. Linking shared data from different tables allows you to display related data on a single line in the resulting report. In other words, by linking my two tables, my report will show not only the driver data, but data on the car that particular driver is using, all on one line in the report.
To link two fields, drag a matching field from one table, and drop it on the matching field in the second table. Lines connect the two fields, showing that they are linked. In the example, the CarNumber field in the Cars table is linked to the CarNumber field in the Drivers table.
Make any other changes you want and then click the Run Query button on the Query Design toolbar to run the query and display a report. The report appears at the top of the window. If it's not right, make other changes to the criteria and run the query again.
| |
