53
Figure 3-1 Microsoft Access startup window
Color profile: Generic CMYK printer profile Composite Default screen
Once you have used Microsoft Access to open one or more database files, a list of the most recently used database files will appear in a list just below the Open an Ex- isting File option in the startup window. You may reopen a previously used database merely by clicking its filename in the list. After you have opened the database, you will see a screen similar to the one shown in Figure 3-2.
This is the main panel in Microsoft Access. In a column along the left margin with the heading Objects, you can switch the type of database objects listed in the center of the panel. When you start Microsoft Access for the very first time, the default se- lection is Tables. However, from that point forward, Microsoft Access will remem- ber the last type you selected for the database and always return you to that type when you subsequently reopen the database. Briefly, the types shown may be de- fined as follows:
• Tables Relational tables. These hold the actual database data in rows and columns.
54 Databases Demystified
Figure 3-2 Microsoft Access main panel
Color profile: Generic CMYK printer profile Composite Default screen
• Queries Stored database queries. These are calledviewsin nearly all other relational databases.
• Forms GUI forms for data entry and/or display within Microsoft Access.
• Reports Reports based on database queries.
• Pages Web pages for data entry and/or display using a web browser.
• Macros Sets of actions that each perform a particular operation, such as opening a form or printing a report.
• Modules Collections of Visual Basic programming language components that are stored as a unit.
As with many personal computer–based databases, Microsoft Access is not only a database, but also a complete development environment for building and running applications. The database products that run on larger, shared computer systems commonly calledserverstypically donotcome with application-development envi- ronments. Learning to build application programs is well outside the scope of this book, so we will not deal with the Forms, Reports, Pages, Macros, and Modules types at all. We will focus only on the Tables and Queries types in Microsoft Access.
Maintenance of the objects in the database can be performed from this panel, in- cluding the following tasks:
• To add a new object of the type displayed, click the appropriate shortcut near the top of the list. For example, the Tables object list includes a shortcut called Create Table in Design View.
• To delete an existing object, click its name so it is selected and then pressDELETE.
• To display an object, double-click its name.
• To display the definition (design) of an object, click its name so that it is selected and then click the Design View button on the toolbar (the one with the ruler, pencil, and triangle on it).
The Microsoft Access Relationships Panel
Microsoft Access provides the Relationships panel, shown in Figure 3-3, for the def- inition and maintenance of referential constraints between the relational tables. To display this panel, either click the toolbar button (the icon with three tables and lines drawn between them on it) or select Tools | Relationships from the menu bar.
CHAPTER 3 Forms-Based Database Queries
55
Color profile: Generic CMYK printer profile Composite Default screen
56 Databases Demystified
Demystified / Databases Demystified / Oppel/ 225364-9 / Chapter 3
The Relationships panel graphically displays tables, shown as rectangles, and one-to-many relationships, shown as lines between the rectangles. Technically, these are referential constraints (relationshipsbeing only a conceptual term), but be- cause Microsoft calls them relationships on this panel, I will also for consistency.
The symbol “1” shows the “one” side of each relationship, whereas the infinity sym- bol (similar to the number 8 laying on its side) shows the “many” side of each rela- tionship. The relationships may be maintained as follows:
• To add tables that are not displayed, click the Show Table button on the toolbar (the one with a table and bold yellow plus sign on it), and select the tables from the pop-up window.
• To remove a table from the display, click it so that it is selected and then pressDELETE. Note that this doesnotdelete the table or any relationships in which the table participates; it merely removes the table from the panel.
• To add a relationship, drag the primary key in one table to the matching foreign key in another. For recursive relationships, the table must be added to the display a second time, and the relationship must be created between one displayed copy of the table and the other. This looks odd at first, but it is only to facilitate the drag-and-drop method of creating the relationship.
A table shown multiple times on the panel still exists only one time in the database.
• To delete a relationship, click the narrow part of its line and pressDELETE. Selecting relationships can be tricky in Microsoft Access because only the Figure 3-3 The Microsoft Access Relationships panel
Color profile: Generic CMYK printer profile Composite Default screen
narrowpart of the line will work, and you may have to stretch short lines by moving a table on the panel in order to even find the narrow part of the line.
• To edit a relationship, double-click the narrow part of its line. A pop-up window may be used to change various options about the relationship, including toggling enforcement of the relationship as a referential constraint on and off (that is, enabling and disabling the constraint). When a constraint is disabled, the DBMS will not prevent inserts, updates, and deletes from creating
“orphan” foreign key values (foreign key values that have no matching primary key values in the parent table). The DBMS will, however, not permit a constraint to be enabled if there are orphan foreign key values in the child table.
Closing or minimizing the window will make the main panel visible once again.
The Microsoft Access Table Design View
From the main panel, a table may be selected by double-clicking its name. The de- fault display, called the Datasheet View, is shown in Figure 3-4. The data in the table is displayed in the familiar tabular form, and the data may be updated if desired, in- cluding the insertion and deletion of rows. Be careful. There is no “undo” feature—
once you move the cursor from one row to another, any changes you have made can- not be easily reversed.
To see the definition of the table, click the Design View button on the toolbar (the one with the ruler, pencil, and triangle on it). Figure 3-5 shows the Design View for the Employees table.
CHAPTER 3 Forms-Based Database Queries
57
Figure 3-4 Datasheet View (Employees table)
Design View
Color profile: Generic CMYK printer profile Composite Default screen
The Design View for a table in Microsoft Access displays information such as the following:
• Field Name The name of the column.
• Data Type The data type for the column.
• Description A description of the column, typically provided by a DBA.
• Field Size A subtype within the data type. For example, Long Integer and Short Integer apply to the more general Number data type.
• Required Indicates whether the column is optional (that is, whether it may have null values).
• Indexed Indicates whether the column has an index.
• Primary Key Denoted with a small key icon next to the field name (or names) that comprises the primary key.
58 Databases Demystified
Figure 3-5 Design View (Employees table)
Color profile: Generic CMYK printer profile Composite Default screen
Hopefully, you recognized that everything on this panel is metadata. There are many more options than the ones noted here, and Microsoft Access is very clever about hiding and exposing options so only the applicable ones are displayed. Notice that help text in blue automatically displays in the lower-right part of the panel as you move the cursor from one option to another.
Creating Queries in Microsoft Access
As mentioned earlier, stored queries are calledviewsin most databases, but because a view is defined as a stored database query, the Microsoft Access name is techni- cally correct. Always keep in mind that queries do not store any data; instead, the data is stored in the tables. On the main panel, clicking the Queries button (along the left margin) lists all the queries stored in this database, as shown in Figure 3-6.
CHAPTER 3 Forms-Based Database Queries
59
Figure 3-6 Microsoft Access Queries window
Color profile: Generic CMYK printer profile Composite Default screen
Although Microsoft Access offers several ways to create a new query, for begin- ners, the Create Query in Design View option is the easiest to understand. Figure 3-7 shows the Design View for a New Query panel (also called the Query Design View panel) with the Show Table dialog box open.
For every new query, Microsoft Access opens the Show Table dialog box to allow for the selection of the tables and/or queries on which the query will be based (that is, the tables or queries that are to be the source of the data that will be displayed).
Once the tables are added, the Query Design View panel allows for the entry of the specification for the desired query. Figure 3-8 shows the Query Design View panel with the Customers table added.
The Query Design View panel has the following components:
• In the open area at the top of the panel (gray background), a graphical representation of the source tables and/or views and the relationships for the query are shown. Any relationships defined for the tables are automatically inherited here.
60 Databases Demystified
Figure 3-7 Query Design View with the Show Table dialog box visible
Color profile: Generic CMYK printer profile Composite Default screen
• In the grid area in the lower part of the panel, each column represents a column of data that is to be returned in the result set when the query is executed. Rows in the grid area define various options to be applied to the corresponding columns (usage examples are provided in the sections that follow):
• Field The specification for the source of the column. This is normally a table or query column name, but it can also be a constant or an expression similar to calculations used in spreadsheets.
• Table The source table or query name for the column.
• Sort The specification for any sort sequencing for the column (Ascending, Descending, or None).
• Show A check box that controls display of the column. If the box is not checked, the column may be used in forming the query, but does not appear in the query results.
• Criteria The specification that determines which rows of data are to appear in the query results. All conditions placed on the same line must be met for a row of data to be displayed in the query results. Conditions placed on subsequent lines (labeled “or” on the panel) are alternative sets of conditions that will also cause a matching data row to be displayed in the results. The usage of these will likely not make sense until you see the examples that follow, but in short, conditions placed on one line
CHAPTER 3 Forms-Based Database Queries
61
Figure 3-8 Query Design View
Color profile: Generic CMYK printer profile Composite Default screen
are connected with a logical AND operator, and each new line of criteria is connected using a logical OR operator with all the other lines. Said another way, any row that matches the specifications that appear on any one of the criteria lines will be displayed in the query results.
Once the specification is complete, clicking the Run button (the one with the ex- clamation point on it) runs the query and displays the results using the Datasheet View, as already shown in Figure 3-4. To go back to the Query Design View panel, simply click the Design View button (the one with the ruler, pencil, and triangle on it). For most queries, data updates may be applied directly in the Datasheet View table, and they are applied directly to the source tables for the query. If a column in the query results cannot be mapped to a single table column—perhaps because it was calculated in some way—then it cannot be updated in the query results.
The remainder of this section will use a series of examples to demonstrate the powerful features of the Microsoft Access Queries tool. For each example, there is a description of the result desired and the steps required to create the specification for the query on the Query Design View panel. This is followed by a figure showing the completed Design View panel, and another figure showing the results when the query is executed.
Example 3-1: List All Customers
To list the entire Customers table (all rows and all columns), follow these steps:
1. From the main panel with Queries selected in the left margin, double-click the link Create Query in Design View.
2. Perform the following actions in the Show Table dialog box:
• Click Customers to select the Customers table.
• Click the Add button.
• Click the Close button.
3. On the Design View panel, double-click the asterisk in the Customers table template (near the top of the panel).
The completed panel is shown at the top of Figure 3-9 with the query results shown below.
62 Databases Demystified
Color profile: Generic CMYK printer profile Composite Default screen
CHAPTER 3 Forms-Based Database Queries
63
Demystified / Databases Demystified / Oppel/ 225364-9 / Chapter 3
Example 3-2: Choosing Columns to Display
Instead of displaying all columns, we now specify only the ones that we wish to see.
To list the CustomerID, CompanyName, City, Region, and Country columns for all customers (all rows and all columns), follow the steps outlined in this section.
Using the Design View from Example 3-1 as a starting point, modify the query as follows:
1. Remove the existing specification that displays all columns by clicking the small gray rectangle above field name “Customers.*” (which changes the entire column to a black background). Then pressDELETEto remove the column.
Figure 3-9 Example 3-1, “List All Customers” (Query Design View) (top), and the query results (bottom)
Color profile: Generic CMYK printer profile Composite Default screen
64 Databases Demystified
2. For each desired column (CustomerID, CompanyName, City, Region, and Country), double-click the column name in the table shown at the top of the form. An alternative method is to drag and drop the column name from the table shown at the top of the form to the grid in the lower part of the form.
The completed panel and query results are shown in Figure 3-10.
Example 3-3: Sorting Results
In any RDBMS, rows are returned in no particular order unless you request one.
Microsoft Access uses the Sort specification to determine the order in which rows are returned in query results.
Figure 3-10 Example 3-2, “Choosing Columns to Display” (top), and the query results (bottom)
Color profile: Generic CMYK printer profile Composite Default screen
To modify Example 3-2 so that rows are sorted in ascending order by city, region, and country, follow these steps:
1. On the line labeled Sort in the column for City, click in the blank space and select Ascending from the pull-down list.
2. Do the same for the Region column. A simple alternative method is to type Ain the sort specification and pressENTER.
3. Do the same for the Country column.
The completed panel and query results are shown in Figure 3-11.
CHAPTER 3 Forms-Based Database Queries
65
Figure 3-11 Example 3-3, “Sorting Results” (top), and the query results (bottom)
Color profile: Generic CMYK printer profile Composite Default screen
Example 3-4: Advanced Sorting
Looking at the results of Example 3-3, you can see that all the cities are listed in as- cending sequence and that sorting by region and then by country had little effect and would matter only if two cities with the same name existed in different regions and countries. Spoken language not always being logically precise, this is unlikely to be what we meant when we said we wanted the data sorted by city, region, and country.
Instead, we likely wanted all the rows for a country to be together, and for each coun- try, all the rows in a region to be together, and for each region, all the cities to be listed in ascending sequence by name. If we had said sort by citywithin regionwithin country, our intent would have been clearer. Now we need a way to sort by country first, region second, and city last, but city is displayed before region, and region be- fore country. Microsoft Access sorting works on the columns in the query from left to right. How can we accomplish our goal? We can place the Region and City col- umns in the query a second time, use the second copies for sorting, but omit them from the query results using the Show check box.
To modify Example 3-3 so that rows are sorted as discussed, follow these steps:
1. Remove the sort specifications on the existing City and Region columns.
2. Add the Region column again by double-clicking its name in the Customers table.
3. Do the same for the City column.
4. Add the ascending sort specification to the Region and City columns that you just added (the ones to therightof the Country column).
5. Remove the check mark for the Region and City columns that you just added.
The completed panel and query results are shown in Figure 3-12.
Note that most languages are read from left to right, so we naturally expect tabular listings to be sorted moving from left to right, starting with the leftmost column. It is unusual, and perhaps poor human engineering, to sort columns another way. But should you ever need to, you now know how.
Example 3-5: Choosing Rows to Display
Thus far we have been displaying all 91 rows in the Customer table in every query. If we do not wish to see all the rows, displaying all of them is wasteful of system re- sources, especially if we are sorting them. Suppose we only wish to see rows for cus- tomers in London, UK. We can do so using the Criteria line on the Query Design View panel.
66 Databases Demystified
Color profile: Generic CMYK printer profile Composite Default screen
To modify Example 3-4 to limit the rows displayed:
1. On the line labeled Criteria, typeLondonin the leftmost City column. Note that Microsoft Access pays no attention to case when selecting data in que- ries, so you can also enterLONDONorlondonand achieve the same result.
2. On the same line, typeUKin the Country column. It is important to enter the City and Country criteria on the same line because we only want rows returned where the City is “London”andthe Country is “UK.”
CHAPTER 3 Forms-Based Database Queries
67
Figure 3-12 Example 3-4, “Advanced Sorting” (top), and the query results (bottom)
Color profile: Generic CMYK printer profile Composite Default screen