Creating Queries

Return to Tutorial homepage

Creating queries

Query-by-example (QBE)

1. From the Database Window, select “Queries”
2. Click on the “New” button
3. (The wizard in this instance isn’t very helpful, so just click on the “Design View” option)
   
4. In creating a query, one needs to specify at least three things:
   • the tables involved in the queries
   • the columns that will either be part of the output shown to the user OR will be subject to some sort of criterion needed to determine which rows will be displayed in the result
   • the conditions (criteria) rows must satisfy to be included in the result
     

   In the “Show Table” dialog box, you will specify which table(s) contain the data you want to query over, by choosing a table and clicking on the Add button.  If multiple tables are involved in the query, repeat for each table.
   
   When all relevant tables have been selected, click on Close.

5. The Query design should now be displaying the tables you have chosen in the top portion of the window, and an area for further defining the query in the lower portion.
   
6. Suppose we wanted to show the album titles and track titles for all Beatles albums aquired after 1/1/1994.  We will first specify the columns of output (CD_Title, Track_Title) we desire.  We then specify any additional columns on which we will define criteria (CD_Artist, CD_DatePurchased).  We will specify these three columns by making these column names appear in the first two Field: fields in the lower portion of the window:
   
   There are three ways to do this, all yielding the same result:
   • double click on the columns desired in the tables shown in the top portion of the window;
   • click and drag a column from the top portion of the window into some field: field;
   • click on the combo box within the field: field and select a column name.

   Notice that the Show box is checked for CD_Title and Track_Title, but not for CD_Artist or CD_DatePurchased.  This is because we want CD_Title and CD_Track to appear in the output, but CD_Artist  or CD_DatePurchased .
   

7. We are interested not in all CDs, but only in Beatles CDs.  Therefore we are interested only in those CDs satisfying the criterion that the CD_Artist = "Beatles" and CD_DatePurchased > 1/1/1994
   
   Notice that the contents of the Criteria field can show an equality (the value in a row must match this one exactly) or an inequality.  Note also that Access has a peculiar format whenever dates are involved.  Surround the date with pound signs (#) for Access to interpret it as a date value.
   
8. To run the query, select Run from the Query menu, or click on the exclamation point.
9. If you wish Access to prompt you for a criterion each time you run the query, put, in hard brackets, the question you wish Access to prompt the user with, e.g. [Please enter Artist:] could be used in place of "Beatles" to make the system more flexible.  
   
   Each time the query is run, the user would be prompted with the question, "Please enter Artist:"
   
   This technique is actually a "kludge", taking advantage of Access behavior when it doesn't recognize a parameter value, and is not recommended for full production systems.  However, as a short-term measure to add flexibility to a query, it works.

Return to top

Previous Section: Creating Forms
Next Section: Buttons and Switchboards