FunctionX Logo

The US Senate Database: Queries
 

Introduction

A query is a request formulated to a database and the application responds by displaying data that it judges fit for the request. Like a table, a query displays its list as a series of cells created by intersections of columns and rows. This means that a query displays its list as a spreadsheet or a data sheet.

A query is equivalent to creating a list of items. The list can be based on one or more fields that already exist(s) on the database or the list can be created with new fields. To create a query, you first decide how its list would be structured. Based on this, there are three types of columns that a query can display:

  • The simplest field of a query can be based on a column of an existing table or another query. For example, suppose you have a list of customers represented by their first name, last name, telephone number, cell phone number, and email address, to create a list that contains only email addresses, you can create a query that would retrieve that column from the table. A query can also use many of such columns. For example, you can retrieve the last names, first names, and email addresses of customers to create a new list as a query
  • A column can be a combination of the columns of a table or another query. For example, continuing with the table that has customers information, you can combine the last name and the first name to produce a full name. In this case, the column would use an expression to create its list. The expression would appear as LastName + , + FirstName.
  • A column of a query can use a complex combination of columns from one or more existing tables and external values such as constants or functions

 

Simple Queries

As mentioned above, the simplest query consists of selecting columns from an existing table or another query. To create such a query, you can proceed visually or with code. The easiest way is to use the Query Wizard to select the columns. You can also select items using the Design View of a query.
  1. Open the US Senate database created in the previous section
  2. To create a new query, on the main menu, click Insert -> Query
  3. In the New Query dialog box, double-click Simple Query Wizard
  4. In the first page of the wizard, in the Tables/Queries combo box, select Senators
  5. In the Available Fields, double-click LastName, FirstName, OfficeAddress, OfficePhone, and WebSite
  6. Click Next
  7. Change the name of the query to SenatorsOfficeContact and click Finish

 

Expressions on Queries

As mentioned earlier, a column of query can be created by combining columns from its parent table. To create such a column, you would write an expression. A simple expression can use only arithmetic operators. A more advanced expression can include Microsoft Access native operators, SQL operators, and/or functions that Microsoft Access can recognize.

To create a column that combines other fields from a table or another query, in Design View, after displaying the table or query that holds the list of columns, in the top box of the desired column, you can type the appropriate expression. Normally, then expression should have a name that would be displayed on the column header. If you don't provide such a string, Microsoft Access would add a string such as Expr1 or Expr2, etc. Otherwise, you can provide your own name or label. It must preceded the expression and followed by a colon.
  1. To create a new query, on the main menu, click Insert -> Query
  2. In the New Query dialog box, double-click Simple Query Wizard
  3. In the first page of the wizard, in the Tables/Queries combo box, select Senators
  4. In the Available Fields, double-click LastName and YearElected
  5. Click Next
  6. Change the name of the query to SenatorsYearsInTheOffice and click Finish
  7. After viewing the query, switch it to Design View
  8. In the first column, change the LastName string to
    Senator: [LastName] & ", " & [FirstName] and press Enter
  9. Switch the query to Datasheet View to see the result
  10. After viewing the query, switch it to Design View
  11. To create a more complex expression, in the lower section of the window, click YearElected and press Tab
  12. Type 
    Years In Office: IIf(IsNull([    YearElected]),"",Year(Now())-CInt([YearElected]))
    and press Enter
  13. Switch the query to Datasheet
  14. After viewing the query, save and close it

Relations-Based Queries

While tables are used to lay a valuable foundation for the flow of information among objects, queries can take very advantage of the relationships that have been created. Like all other objects, a query can use an already established relationship between fields of two tables. Based on this, you can create a query that combines fields from one or more tables or combining other, existing queries.
  1. To create a new query, on the main menu, click Insert -> Query
  2. In the New Query dialog box, double-click Simple Query Wizard
  3. In the first page of the wizard, in the Tables/Queries combo box, select Senators
  4. In the Available Fields, double-click SenatorID and LastName
  5. In the Tables/Queries combo box, select Genders
  6. In the Available Fields, double-click Gender
  7. Click Next
  8. Change the name of the query to SenatorsByGender
  9. Click the Modify The Query Design radio button and click Finish
  10. In the lower section of the table, change the LastName string to
    Senator: [LastName] & ", " & [FirstName] and press Enter
  11. Switch the query to Datasheet View to view the result
  12. Save the query and close it
  13. On the Database window, click Forms
  14. To design a query that uses various tables, on the main menu, click Insert -> Query
  15. In the New Query dialog box, double-click Design View
  16. In the Show Table, double-click States, Senators, and Parties
  17. Click Close
  18. In the lists that display in the top section of the window, click any field and press F6
  19. In the first column where the caret is, type Senator: [LastName] & ", " & [FirstName]
  20. From the Parties list, drag Party and drop it in the second column
  21. From the States list, drag State and drop it in the third column
  22. Save the query as SenatorsPartyAndState and switch it to Datasheet View
  23. Close the query
  24. Using the same technique, create a query based on the Senators, the States, and the Parties tables. Set the expression of the first column to
    Senator: [LastName] & ", " & [FirstName]
    Set the second column to Party from the Parties table, the third as State from the States table, the fourth as MaritalStatusID from the Senators table, and the last as GenderID from the Senators
     
  25. Save the query as SenatorsMaritalStatus and switch it to Datasheet View
  26. Close the query
Previous Home Next