Using a Query to Combine Fields From Different Tables

From NeoWiki

Revision as of 01:43, 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.

-->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.
Personal tools