Using Databases to Solve Problems
2.2 Querying a Database in MS Access
Thus far we have discussed the structure of relational database and how to plan and create a database. Once the database is created and data placed in the appropriate table, we will need to be able to extract data. The data in a database can be retrieved by examining its tables; however, all the information contained in a table is frequently more information than is needed by a database user. In addition, database users often want to view information that spans related tables, or view data in a different order. Queries provide a way for users to obtain more meaningful information from a database that simply viewing all data contained in a table. Queries can be used to:
· Filter data by selecting specific records according to criteria.
· Sort records.
· Group records together (aggregate) to perform calculations such as Sum, Average, Count, Min, Max, etc.
· Perform arithmetic and Boolean calculations.
· Extract information from more than one table by joining related tables.
This chapter will cover the first four bullets; the last item, joining tables, will be discussed in subsequent chapters.
What is a Query
Remember that a table is a database object that stores records of related data according to a prescribed field order. A query is also an Access database object; a query defines how data will be extracted from existing data tables or the results of previous queries and how the extracted data will be organized. In English, a query is a question. In a database, a query is a set of instructions the computer will follow to answer a question about the data in a database. The answers themselves are dynamic, it changes whenever the data in the database changes, and thus is not part of the query but rather the result of the querying operation. The results of a query are often referred to as a dynaset.
Specifying and performing a query involves the use of relational algebra operations such as select, project, join, etc. Several languages exist that can be used to create database tables, relationships, and queries. One of the oldest is SQL, which stands for Structured Query Language (pronounced "SQL" or "see-qwill"). It was originally developed by IBM for use on its mainframe computers. Fortunately, in modern day databases, we will neither have to understand how to write instructions in SQL or the process by which the computer actually obtains the dynaset. A user of Microsoft Access can specify a query using a tool called Query by Example (QBE). This approach makes use of the graphics capabilities of the computer (GUI interface) to allow the user to select the elements required for a query. The software then translates QBE grids into SQL for processing by the computer.
Before we can begin to use the query tool, we or someone else must have already completed the following tasks:
· Created tables with defined fields, field properties, and primary keys.
· Entered data into the tables.
· Set up relationships between the tables using foreign key(s).
The next section will describe a sample database that meets these requirements and will be used to demonstrate the query throughout the chapter.
A Sample Database
In order to illustrate some of the query operations we will utilize the sample database seen in Figure 1.
Members
ID# / Name / Phone / Address / Active / Join Date635 / Acquila, Jose / 614-927-8345 / Pickerington, OH / Yes / 1/5/97
534 / Adler, Lawrence / 614-746-2385 / Pataskala, OH / Yes / 3/4/99
345 / Amico, Donald / 419-345-8472 / Toledo, OH / Yes / 1/5/97
546 / Anderson, William / 513-345-9384 / Cincinnati, OH / No / 4/10/00
903 / Applegate, Bernice / 513-485-3833 / Cleveland, OH / Yes / 3/17/01
002 / Aston, Martin / 614-485-3888 / Columbus, OH / No / 11/7/98
047 / Attwater, Lester / 614-292-1442 / Columbus, OH / Yes / 9/1/00
Finances
ID# / Dues-Paid / DonationFigure 1: Club Database – Database View of Tables
635 / 100 / 0534 / 50 / 0
345 / 100 / 50
546 / 0 / 250
903 / 0 / 0
534 / 50 / 20
This database contains two tables: Members and Finances. The table structures are as follows:
· Members(ID#,Name,Phone,Address,Active,Join Date) This table is a membership list of all active and retired members. The ID# is a Number data type and has been identified as the primary key for this table. The Active field is a “Yes/No” Boolean data type field (a yes indicates this is an active member). The Join Date field is a date data type field. All other fields in the Members table are Text.
Note that when typing a Boolean field into an Access table, the column is usually displayed as check box. In this case, a check indicates that this is an active member; no check indicates a past or retired member. The actual values stored are the Boolean values. If an Access table with a Boolean field is copied into an Excel spreadsheet, the Boolean values are translated to True/False cell entries.
· Finances(ID#,Dues-Paid,Donation) This table contains records of member transactions. The amount of dues paid (full or partial payment) is entered in the Dues-Paid field. Dues for the year are $100 for active members; retired members are not charged dues. Additional contributions are recorded in the Donations field. The ID# field is type Number and Dues-Paid and Donation fields are type Number. A member may make any number of transactions.
Both tables contain and an ID# field representing the same information. Both these fields are defined as type Number. ID# is the primary key of the Members table. Thus all three requirements are met to set up a relationship (same information, same data type, primary of at least one table). The ID# fields create a foreign key relating the two tables, as diagrammed in Figure 2. We have now completed setting up the database relationships and are ready to ask questions about the database.
Figure 2
Using the Query Design Tool
In Access, the query object contains the instructions the computer will carry out to answer a specific question about the data in an Access database. These instructions tell the computer what set of data needs to be examined, and what filters and/or sorts to apply. Executing an Access query results in a dynaset, a set of data based on the most recent information in the database.
The Query by Design tool structure
There are two different ways to use the query tool in Access; one way is to follow a set of steps in the Query Wizard and the other is to use the Query Design tool. Both of these options can be launched from the Create ribbon in the Other group. This chapter will deal exclusively with the latter option, the Query Design tool. Figure 3 displays the Query Design tool window including the Show section and the QBE (query by example) grid. Figure 4 displays the content sensitive Design ribbon containing the different design commands available.
In the upper portion of Design window, the data objects needed for the query (either tables or previously created queries) are displayed. In the lower portion on the QBE grid, the user specifies the fields, criteria, sorting, and information needed to carry out the query. This section is divided into a series of rows as follows:
· FIELD: Lists of all the fields involved in the query.
· TABLE: For each field entered, the table it came from must be listed. Sometimes two tables may have a field with the same name so the table is necessary to distinguish the fields.
· TOTAL: This line is normally not shown. It can be toggled using the S toolbar button. It is used in queries that require grouping of data.
· SORT: This line is used when sorting. One or more fields can be organized in Ascending or Descending order.
· SHOW: This box is checked by default and is used to specify whether or not the field will be shown in the resulting dynaset.
· CRITERIA: This specifies criteria that a field must meet in order for a record to be included in the resulting dynaset. e.g., Donation >= 200.
· OR: This specifies any alternate (Boolean Or) criteria.
The mechanics of selecting the data objects and selecting the appropriate fields is covered in the course textbook. Here, the focus will be on which design elements are needed to solve specific problems.
filling out the query grid
Table: MembersField / Name / Address
Table / Members / Members
Sort
Show / x / x
Criteria
OR
Figure 5
The design grid in Figure 5 is a query that lists the Name and Address fields from the Members table. The Show line indicates that both of these fields will be displayed in the resulting dynaset. The other fields on the Members table are not listed on the grid and therefore will play no role in either the dynaset display or the selection of records. The resulting dynaset for this query will display these two fields for all records in the Members table, as there are no criteria specified to filter the data.
How can a question be translated onto the QBE grid? As with any problem solving activity, it helps to have a methodology containing a set of logical steps to follow. The following is a simplified method for taking a question and translating it into a QBE grid. This procedure will be extended in later chapters to allow for the use of multiple tables in a query.
· Step 1: Plan the QBE grid:
When filling out the QBE grid it is always wise to plan ahead, since the next task will be to select the table to be included in the query. In order to know which table (or previous query) is required, the following information must be known:
o What fields are to be shown in the resulting query and in which table (or previously written query) can these fields be found?
o What field(s) will be filtered and therefore what criteria should be specified for that field? For example, if only a list of active members is desired, the criterion Yes must be specified in the field Active.
· Step 2: Add the Table:
Using the Show table feature add the table(s) or saved query required for the query. The Show Table dialog box can be launched using the Show Table button on the Query Setup group of the Design ribbon. The Show Table dialog box can be seen in Figure 6. Select the desired table (or query) object in the dialog box by double clicking on the object name or clicking on the object and then the Add button.
Figure 6
· Step 3: Fill out the Grid:
1. List each of the fields to be shown on the resulting dynaset. Select the fields from the table(s) shown. The detailed mechanics are described in the course text.
2. List any fields not shown but that will contain criteria. Make sure to uncheck the show box for such fields.
3. Write the necessary criteria in the appropriate fields using the Criteria and OR lines.
4. Use the Sort feature, Group by feature, and Expression builder as needed.
· Step 4: Save and name the query.
Select queries
This section discusses writing queries that select specific records based on a set of criteria. This includes a single select criterion in a single field, multiple criteria in a single field, and multiple criteria in multiple fields.
Queries with a Single Criterion
First, the methodology described in the previous section will be used to create a query containing a single condition on a single field. A condition can be specified by using the Criteria line of the QBE grid. Consider the following example:
Name / AddressAcquila, Jose / Pickerington, OH
Adler, Lawrence / Pataskala, OH
Amico, Donald / Toledo, OH
Applegate, Bernice / Cleveland, OH
Attwater, Lester / Columbus, OH
Write a query to select all active members, listing their names and addresses. The results should be similar to structure/data listed in Figure 7.
Figure 7
· Step 1: Plan the QBE grid. The query specifies that the Name and Address fields are to be shown in the resulting dynaset. Both are found in the Members table. In addition, a criterion that only “active members” should be included on this list is specified, requiring a condition on the Active field (i.e., that it is equal to “Yes”), which also is on the Member’s table.
· Step 2: Add the Member’s table on the QBE design view in Access. All information needed to answer the question is found in this table
· Step 3: Select the appropriate fields (Name, Address, and Active) and place them in the grid. Since Name and Address are to be shown in the dynaset, be sure the Show checkbox for each is checked. Uncheck the show box for Active, as this field requires a criteria but is not asked to be shown in the final result.
Fill in the selection criteria on the Criteria line of the QBE grid. Place the criteria, in this case, in the Active field column on the Criteria line. Since this is a Boolean type field the criterion can be either Yes or No. Since only Active members are specified, the criterion will be Yes. Quotes should not be used around Yes since it is a value in a Boolean field and not a text label.
· Step 4: Save the QBE grid by naming the query Query1. The final version of the QBE grid can be seen below (Figure 8).
Tables: MembersField / Name / Address / Active
Table / Members / Members / Members
Sort
Show / x / x
Figure 8 – Query 1
Criteria / YesLogically, what steps are required to obtain this dynaset? To obtain this result, one would systematically go through each record of the Members table and test to see if the criteria are met. If the criteria are met (in this example, the member is active), then the appropriate fields for that record should be displayed. Notice that the records for Aston and Anderson do not appear since they are not active members.