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.

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