Using a Query to Combine Fields From Different Tables

From NeoWiki

While we have created separate tables for Sightings, the AOU Birdlist, Field Trips, etc., we will often want to see information about our bird sightings that involves fields from many of these tables. One way to view fields from multiple tables at once is by using Queries. This article describes creating a relatively simple Query.

Contents 1 Selecting Fields for the Query 2 Setting Query Sorting 3 Specifying Criteria for the Query 4 Saving the Query 5 Next Steps 6 Queries or Filters in Forms? 7 Query Wizard or Design View?

Selecting Fields for the Query

1. Click on the Queries icon in the left hand column of the main database document window.
2. Under the Tasks section, click on Create Query in Design View...

   -->The Query Design window opens, as does the Add Table or Query window.

3. One at a time, click on each of the following tables and then click on Add

   AOU_Birdlist

   Sightings

   Field_Trips

   Locations

   --> the windows appear in the Query Design window.

   --> Note that you can also add queries by clicking on the Queries radio button

4. Click the red Close button on the Add Table or Query window.
5. If desired, you can resize the "windows" for each of the tables, as well as move them around.

   -->If you have already established relationships, you will see lines connecting the tables. If you have not done so already, you can drag a field from one Table or Query to the corresponding field in another Table or Query.

6. One at a time, drag the following fields to a blank "field" cell in the bottom part of the Query Design Window. (The tables they come from are noted in parentheses)

   AOU_ID (AOU_Birdlist)

   Bird_Name (Sightings)

   Male (Sightings)

   Female (Sightings)

   Juvenile (Sightings)

   Voice (Sightings)

   Sight (Sightings)

   Date (Field_Trips)

   Field_Trip_Location (Field_Trips)

   StateOrProvince (Locations)

   -->Note that sometimes dragging does not work well. If this is the case, you can control-click or right-click in the Field cell and choose the field you want from the list that appears. If you set the Table in the Table corresponding Table cell, the field cell list will contain only fields from that table.

7. In the Field_Trip_Location column, in the Alias row, type Location. this provides a shorter alias for this field.

-->Note that if you need to, you can add Tables by clicking on the Add Tables button on the toolbar. It looks like a blue table with cross at the upper left hand corner.

Setting Query Sorting

You can set the Query to be ordered by any field in the Query. In this example, we will have a "two level" sort.

1. Click in the cell in the Sort row under the AOU ID column
2. Choose Ascending from the drop-down menu.
3. Now click in the cell in the Sort row under the Date column
4. Choose Ascending from the drop-down menu.
   
   

Specifying Criteria for the Query

The Criterion and Or rows let you specify what data you want the Query to access. Below are several examples. To see the effect of each criterion, click on the Run Query button on the Toolbar. (The data sources icon with a green arrow pointing down). The top section of the window will display the results.

• To Find only those birds identified by Voice, type TRUE in the Criterion row of the Voice column.
• To Find only those birds whose gender is not identifiable (e.g. Robins, Blue Jays), type FALSE in the Criterion row of the Male and Female columns AND type NULL in the first Or row of the Male and Female columns.

  -->Note that BOOLEAN (or Yes/No) fields actually have three settings: TRUE, FALSE, and NULL. When entering data in forms and tables, it is impossible to tell the difference between a FALSE and a NULL entry. So you must build your Queries or your filters to take this into account.

• To find only those birds seen after 5/1/1900, enter > #5/1/1900# in the Criterion row of the Date column.

  -->Note that dates must be surrounded by # # in order for Base to recognize them as dates. Then you may use mathmatical expressions such as <,>,=,>=, etc.

• To find only those birds seen in a particular state or province, type the name of the province in single quotes in the Criterion row of the StateOrProvince column. Using our test data, for example, one could enter 'BZ'. You can add additional states or provinces by typing in their codes in the Or rows of the same column.
• If you were designing the database to track sightings of more than one birder, and had instead of Voice and Sight fields named User1Voice, User1Sight, User2Voice, Usert2Sight, etc., you could have the query only select birds seen by User1 by typing TRUE in the Criterion row of the User1Voice column and TRUE in the first Or row of the User1Sight column. If you placed both TRUEs in the Criterion row, you would pull only those birds identified (on a given field trip) by both Voice AND Sight.

Saving the Query

1. Before closing the Query, clear any content in the Criterion and Or rows. (It is perfectly appropriate and possible to save a query with specified criteria. For the purposes of the tutorial, however, you will need this Query, without any restricting Criteria, to follow the directions in the Creating a Form in Design View article.)
2. Click on the disk drive icon on the toolbar to save the Query.
3. You will be asked to name the query; name it All Sightings

Next Steps

Creating a Form in Design View

Queries or Filters in Forms?

As you can see, the options for Queries are almost endless. The number of Queries could quickly become unmanagable if you created one for every contingency. Fortunately, Queries are not the only way to pull only selected data from your database. You can also use filters in forms.

Query Wizard or Design View?

This tutorial has discussed building a Query in Design view. You can also build Queries using the Wizard. In some ways this is easier. The wizard works quite well for Queries built on one table. Unfortunately, I have been unable to get the Wizard to finish the process if fields I attempt to use fields from more than one table.