Using a Query to Combine Fields From Different Tables

From NeoWiki

Revision as of 02:51, 27 September 2007 by Lorinda (Talk | contribs)
Jump to: navigation, search
This article is part of the To Base and Back Again tutorial series.

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.

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 Tables 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)
    ID (AOU Birdlist)
    Bird Name (Sightings)
    Male (Sightings)
    Female (Sightings)
    Juvenile (Sightings)
    Voice (Sightings)
    Sight (Sightings)
    Date (Field Trips)
    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 ID column, click in the row labeled Alias and type AOU ID. The Alias row lets you give more descriptive names for the fields you are using. In this case, we used it to clarify which ID is being used.

-->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

  • 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 fiels named User1Voice, User1Sight, User2Voice, Usert2Sight, 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.
Personal tools