Using a Query to Combine Fields From Different Tables

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 23:47, 20 September 2007 (edit)
Lorinda (Talk | contribs)
(begin article)
← Previous diff
Revision as of 02:55, 26 September 2007 (edit) (undo)
Lorinda (Talk | contribs)
(Creating the Query - continue work on article)
Next diff →
Line 3: Line 3:
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. 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.
-==Creating the Query==+==Selecting Fields for the Query==
 +#Click on the {{Button|Queries}} icon in the left hand column of the main database document window.
 +#Under the {{Section|Tables}} section, click on '''Create Query in Design View...'''
 +#:''-->The {{Window|Query Design}} window opens, as does the {{Window|Add Table or Query}} window.''
 +#One at a time, click on each of the following tables and then click on {{Button|Add}}
 +#:'''AOU Birdlist'''
 +#:'''Sightings'''
 +#:'''Field Trips'''
 +#:'''Locations'''
 +#:''--> the windows appear in the {{Window|Query Design}} window.''
 +#:''--> Note that you can also add queries by clicking on the {{prefName|Queries}} radio button''
 +#Click the red Close button on the {{Window|Add Table or Query}} window.
 +#If desired, you can resize the "windows" for each of the tables, as well as move them around.
 +#:''-->If you have already [[Establishing Relationships|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.''
 +#One at a time, drag the following fields to a blank "field" cell in the bottom part of the {{Window|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 {{Button|Add Tables}} button on the toolbar. It looks like a blue table with cross at the upper left hand corner.''
 + 
 + 
 +==Setting Query Sorting==

Revision as of 02:55, 26 September 2007

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

Personal tools