ۥ- *x///////6eeee,   ~HI4}$4աU**_sUask coursetitle "What is the TITLE of this course? Use this format for capitalization: How to Capitalize the Title"Database Building Blocksref coursetitleDatabase Building Blocks ask coursenum "What is the COURSE NUMBER? (Example: EZ101)"AC211Presented by: ask presenter "Who is presenting this course? (Press Shift+Enter after each name)"David Kaplanref presenterDavid Kaplan ask title "What is the TITLE of this course? Use this format for capitalization: How to Capitalize the Title"Database Building Blocksask coursenumber "What is the COURSE NUMBER? (Example: EZ101)"AC211ask title "What is the TITLE of this course? Use this format for capitalization: How to Capitalize the Title"Database Building Blocksask coursenumber "What is the COURSE NUMBER? (Example: EZ101)"AC211 Abstract Relational databases enforce the integrity and uniqueness of data, but at a cost. For example, a well-structured database will store a customers name and address only once, regardless of how many orders the customer places. This guarantees that changes to the customers information are automatically shared across the entire system, and that users dont have to deal with redundant and possibly conflicting data. However, maintaining the uniqueness of data items requires complex database structures which developers find time-consuming to create and maintain, and which end users find daunting. A properly structured database includes strange-sounding things like normalized tables, primary keys, foreign keys, and referential integrity constraints. These tools are used by the database developer to create one-to-many and many-to-many relationships. All relational database structures, regardless of the particular application, are composed of a series of these relationships. These one-to-many and many-to-many constructs are the primary building blocks of relational database applications. For example, an interesting one-to-many might list people and their accomplishments: People  Awesome Accomplishments   Who  When What  Harriet Tubman symbol 232 \f "Wingdings" \s 8 1849 Escaped from slavery   symbol 232 \f "Wingdings" \s 8 1850-60 Helped 300 others to escape   symbol 232 \f "Wingdings" \s 8 1861-65 Helped Union army as cook, nurse, spy, and scout       Babe Ruth symbol 232 \f "Wingdings" \s 8 1914-19 92-44 won-lost record as a pitcher   symbol 232 \f "Wingdings" \s 8 1927 60 home runs in a season   symbol 232 \f "Wingdings" \s 8 1914-35 714 career home runs       Marie Curie symbol 232 \f "Wingdings" \s 8 1898 Discovered radium   symbol 232 \f "Wingdings" \s 8 1903 Won Nobel prize in physics       Michael Jordan symbol 232 \f "Wingdings" \s 8 1991 One-peat   symbol 232 \f "Wingdings" \s 8 1992 Two-peat   symbol 232 \f "Wingdings" \s 8 1993 Three-Peat  Source: Microsoft Encarta 94  And, an interesting many-to-many could relate famous pairings down through history: Girls   Boys         First Name Last Name  First Name Last Name  Cleo Patra  Marc Antony  Anne Boleyn  Julius Caesar  Katherine Aragon  Henry VIII  Mary Todd  Abe Lincoln  Juliet Capulet  Romeo Montague        Sources: Microsoft Encarta 94 Scientific American, Jan 94  This paper describes the tools available in Microsoft Access to quickly create and maintain these essential database building blocks, and highlights ways in which you can hide the complexity of the underlying database structure from end users. Database Basics This paper assumes that you are generally familiar with the basics of relational databases, particularly those noted above: one-to-many and many-to-many relationships, normalization, primary and foreign keys, and referential integrity. This section presents a brief review of these concepts. References If you would like additional background, you can acquire it from sources like the following: Tech Ed courses like AC101, Introduction to Relational Database Design Microsoft Access Users Guide, particularly the chapters discussing database, table, and schema design Books like Introduction to Database Systems, by C. J. Date Terminology: 1M and MM Because they are so central to the discussion, it helps to have a short-hand for one-to-many and many-to-many relationships. So, when I say 1M, you should think one-to-many. When I say MM, please think many-to-many. One-to-Many Relationships One-to-many relationships occur constantly in databases. Examples include customers and orders, employees and tasks, states and cities, mothers and children, teams and players, trees and leaves. In each of these pairings, an instance of the first object can own (in some sense) one or more instances of the second object. However, an instance of the second object can only be owned by a single parent object. The example below shows two customers, each of which has placed several orders. Customers   Orders     Customer ID Company Name  Customer ID Order Number Order Date Amount  GOURL Gourmet Luncheonettes symbol 232 \f "Wingdings" \s 8 GOURL 10777 08-Nov-93 $3.01    symbol 232 \f "Wingdings" \s 8 GOURL 10790 15-Nov-93 $28.23    symbol 232 \f "Wingdings" \s 8 GOURL 10959 09-Feb-94 $4.98    symbol 232 \f "Wingdings" \s 8 GOURL 11049 18-Mar-94 $8.34          GREAL Great Lakes Food Market symbol 232 \f "Wingdings" \s 8 GREAL 10528 30-Mar-93 $3.35    symbol 232 \f "Wingdings" \s 8 GREAL 10589 28-May-93 $4.42    symbol 232 \f "Wingdings" \s 8 GREAL 10616 24-Jun-93 $116.53    symbol 232 \f "Wingdings" \s 8 GREAL 10617 24-Jun-93 $18.53    symbol 232 \f "Wingdings" \s 8 GREAL 10656 29-Jul-93 $57.15    symbol 232 \f "Wingdings" \s 8 GREAL 10681 19-Aug-93 $76.13    symbol 232 \f "Wingdings" \s 8 GREAL 10816 30-Nov-93 $719.78    symbol 232 \f "Wingdings" \s 8 GREAL 10936 31-Jan-94 $33.68  The Role of Keys The key fieldCustomer ID in this exampleplays a special role in 1M relationships. In the list of customers on the left, Customer ID is the primary key, or the unique identifier for each customer in the list. In the list of orders on the right, Customer ID is the foreign key, or the short-hand entry that ties each order to exactly one customer. In a properly structured database, no other information about the one table (in this case, Customers) appears in the many table (in this case, Orders) except the foreign key field. All other information associated with a customerCompany Name, Address, Phone Number, and so onis stored once and only once in the Customers table. (If it is not obvious why, see the brief discussion below on Normalization, or one of the References for more detail.) Many-to-Many Relationships Many-to-many relationships occur often in database applications, although they are sometimes not as obvious as 1M. Obvious examples include students and courses, employees and projects, authors and titles, parents and children, countries and languages. A common trait of all these obvious MM examples is that they correspond to a real-world assignment of some sort: enrolling students in courses, assigning employees to projects, and so on. Unlike with 1M, there is no ownership of any object by another. Instead, any object can relate to multiple instances of the other object. The example below shows three students, each of whom is enrolled in several different courses. Students    Courses   Student ID First Name Last Name  Course ID Course Name         87 Nancy Davolio symbol 232 \f "Wingdings" \s 8 PH101 Intro to Physics     symbol 232 \f "Wingdings" \s 8 GEO99 Rocks for Jocks         95 Robert King symbol 232 \f "Wingdings" \s 8 ENG202 American Novel     symbol 232 \f "Wingdings" \s 8 PH101 Intro to Physics     symbol 232 \f "Wingdings" \s 8 GEO99 Rocks for Jocks     symbol 232 \f "Wingdings" \s 8 MA1 Math Blaster         134 Laura Callahan symbol 232 \f "Wingdings" \s 8 ENG202 American Novel     symbol 232 \f "Wingdings" \s 8 PH101 Intro to Physics     symbol 232 \f "Wingdings" \s 8 GEO99 Rocks for Jocks  However, and this is characteristic of MM, it makes just as much sense to view this relationship in the opposite direction; in this case, as a series of courses each with several students enrolled in it. Courses   Students    Course ID Course Name  Student ID First Name Last Name         PH101 Intro to Physics symbol 232 \f "Wingdings" \s 8 87 Nancy Davolio    symbol 232 \f "Wingdings" \s 8 95 Robert King    symbol 232 \f "Wingdings" \s 8 134 Laura Callahan         GEO99 Rocks for Jocks symbol 232 \f "Wingdings" \s 8 87 Nancy Davolio    symbol 232 \f "Wingdings" \s 8 95 Robert King    symbol 232 \f "Wingdings" \s 8 134 Laura Callahan         ENG202 American Novel symbol 232 \f "Wingdings" \s 8 95 Robert King    symbol 232 \f "Wingdings" \s 8 134 Laura Callahan         MA1 Math Blaster symbol 232 \f "Wingdings" \s 8 95 Robert King  The Role of Keys The key fieldsStudent ID and Course ID in this exampleplay special roles in MM, just as in 1M. Here, however, these roles are a bit more complex than in the 1M case. With MM, there is no way to store key field references in the other table, because you would need an indeterminate number of foreign key columns. Instead, we create a third table, often called a junction table or intersection table, whose job is to store the pairings of only those primary objects assigned to one another. For the data in this example, the junction table is: Students-Courses   Student ID Course ID  87 PH101  87 GEO99  95 ENG202  95 PH101  95 GEO99  95 MA1  134 ENG202  134 PH101  134 GEO99  It is customary to name the junction table by the concatenation of the primary table names. With the junction table out in the open, it is now easy to see that any MM is actually implemented as a pair of 1Ms. In this case, there is a 1M between the Students table and the junction table, and another 1M between the Courses table and the junction table. Subtle Many-to-Many I mentioned above that some MMs are not as obvious as others. The students-courses example is obvious because the assignment of students to courses is based on a real-world practice. Other obvious examples include the assignment of employees to projects, which again is based on a real-world practice. Each obvious example like this includes a junction table like the one above, whose primary (often only) role in life is to store pairs of key values from the two main tables. However, there are also many subtle, non-obvious MMs in database systems. In fact, there are typically far more of these than of the obvious kind. While they do not generally correspond directly to real-world assignment operations, these subtle MMs are very useful in their own right. For example, think about the Nwind sample database that comes with Microsoft Access. It contains a subtle MM relationship between Customers and Employees, that expresses which customers have placed orders with which employees. In this instance, the Orders table is the junction table, because it stores the unique pairings of Customer ID and Employee ID (even though its main job is to store detailed information about each order, not to be a junction table). You can extend this idea almost ad infinitum. For example, Nwind also contains a subtle MM between Customers and Products that expresses which customers have placed orders for which products. In this case, the junction table is actually a query, the two-table join of Orders and Order Details. This query contains unique pairings of Customer ID, from the Orders table, and Product ID, from the Order Details table. The data in subtle MMs is not entered directly into the system in normal use. For example, no one sits down and explicitly assigns customers to products, as is done with students and courses. Rather this data emerges as a byproduct of other data entry operations. In the Nwind examples, the normal order entry process captures customer, product and sales rep (employee) data about each order, and this data can then be used to examine the relationships between Customers and Employees or between Customers and Products. Often, the information contained in these subtle MM relationships is among the most valuable in your system. For example, imagine you own the Northwind Traders Company (subject of the fictitious Nwind database). You would certainly value a quick way to examine the patterns of which sales reps are selling to which customers, and which customers are buying which products. As we shall see, Microsoft Access makes it as easy to deal with these subtle MMs as with the more obvious and straightforward assignment variety. Normalization Fields that are neither primary nor foreign keys are often called dependent fields. Normalization is the process of designing tables such that each dependent field is stored only in the table where it is unique for each record. In Nwind, for example, Contact Name and Phone Number are dependent fields stored in the Customers table. These fields are unique for each customer but non-unique for each order (since there can be multiple orders per customer). If Contact Name and Phone Number were stored with each order record, it would lead to redundant and possibly conflicting data. The basic idea behind normalization is put the data where it uniquely belongs (and not where it causes redundancy). In other words, these tables are nicely normalized... Customers   Orders     Customer ID Company Name  Customer ID Order Number Order Date Amount  GOURL Gourmet Luncheonettes symbol 232 \f "Wingdings" \s 8 GOURL 10777 08-Nov-93 $3.01    symbol 232 \f "Wingdings" \s 8 GOURL 10790 15-Nov-93 $28.23          GREAL Great Lakes Food Market symbol 232 \f "Wingdings" \s 8 GREAL 10528 30-Mar-93 $3.35    symbol 232 \f "Wingdings" \s 8 GREAL 10589 28-May-93 $4.42    symbol 232 \f "Wingdings" \s 8 GREAL 10681 19-Aug-93 $76.13  ... while these tables are a database Bozo no-no (and just look at what it did to the data!). Customers  Orders     Customer ID  Company Name Order Number Order Date Amount         GOURL symbol 232 \f "Wingdings" \s 8 Gourmet Luncheonettes 10777 08-Nov-93 $3.01   symbol 232 \f "Wingdings" \s 8 Gourmet Luncheonettes 10790 15-Nov-93 $28.23         GREAL symbol 232 \f "Wingdings" \s 8 Great Lakes Food Market 10528 30-Mar-93 $3.35   symbol 232 \f "Wingdings" \s 8 Great Lakes Feed Market 10589 28-May-93 $4.42   symbol 232 \f "Wingdings" \s 8 Great Elmer Fudd Market 10681 19-Aug-93 $76.13  Hence, normalization delivers the major benefit of relational databases, namely the ability to ensure the integrity of your data. (However, in practice, most databases are slightly de-normalized. For example, City and State are often entered as straight text fields, rather than as foreign key references to a Cities or States table.) Referential Integrity (RI) Referential integrity means never having to say you are sorry that you lost data: I know its in that dang database somewhere, I just cant find it! More precisely, RI means that foreign key values only contain valid primary key values, for example, that there are no order records in the database assigned to a non-existent customer. Databases offer varying degrees of referential integrity enforcement. The most basic form is to prevent deletion of primary records with foreign key references, and to prevent editing the primary key value in such records (usually called restrict RI). More advanced forms of referential integrity are to cascade primary key updates to all affected foreign key references, or to cascade the deletion of foreign key records when the primary record is deleted (not too surprisingly called cascade RI). So if you want to be referentially integral (my term, not Ted Codds!), you do it like this ... Customers   Orders     Customer ID Company Name  Customer ID Order Number Order Date Amount  GOURL Gourmet Luncheonettes symbol 232 \f "Wingdings" \s 8 GOURL 10777 08-Nov-93 $3.01    symbol 232 \f "Wingdings" \s 8 GOURL 10790 15-Nov-93 $28.23          GREAL Great Lakes Food Market symbol 232 \f "Wingdings" \s 8 GREAL 10528 30-Mar-93 $3.35    symbol 232 \f "Wingdings" \s 8 GREAL 10589 28-May-93 $4.42    symbol 232 \f "Wingdings" \s 8 GREAL 10681 19-Aug-93 $76.13  ... and not like this ... Customers   Orders     Customer ID Company Name  Customer ID Order Number Order Date Amount  GOURL Gourmet Luncheonettes symbol 232 \f "Wingdings" \s 8 GOURL 10777 08-Nov-93 $3.01    symbol 232 \f "Wingdings" \s 8 GOURL 10790 15-Nov-93 $28.23          GREAL Great Lakes Food Market symbol 232 \f "Wingdings" \s 8 GREAL 10528 30-Mar-93 $3.35    symbol 232 \f "Wingdings" \s 8 GREAL 10589 28-May-93 $4.42    ? BOZO 10681 19-Aug-93 $76.13  The User-System Dilemma The ability to normalize and enforce referential integrity makes relational databases very powerful. It also makes them very unapproachable to most end users. Since the spreadsheet is so well-understood, many users expect databases to behave similarly. They expect to see their data in a familiar, everyday formata tabular list of customers and orders, for example. On the other hand, databases need to store data in separate normalized tables, and then go through complex gyrations to present it the way users want. A large part of any relational database product is designed to help the database developer create powerful database structures, like those described above. The remainder, which itself accounts for a substantial portion of many products, is designed to help both developers and users deal with the complexity of these structures. This dilemma between complexity of structure and the simplicity of user requirements is the major challenge to developing great database software. Microsoft Access Tools Microsoft Access includes lots of tools for creating powerful database structures and hiding complexity from end users. This section covers the major ones briefly. For more information on any of these, see the Microsoft Access documentation or any of a number of good books on Microsoft Access. Primary keys and foreign keys Microsoft Access table design lets you specify single- or multi-column primary keys. When you establish a one-to-many system relationship in Microsoft Accesss graphical schema editor, the column(s) joined to in the secondary table are, by definition, the foreign key. Indexes The Microsoft Access index editor, a tool available in table design, lets you specify single- or multi-column indexes, which can be either unique or non-unique. A Microsoft Access primary key is also a unique index, but you can have multiple unique indexes on a single table. Referential Integrity The Microsoft Access graphical schema editor (system relationships window) lets you specify both restrict and cascade referential integrity, including both cascade updates and cascade deletes. Cascade referential integrity supports multiple levels of cascading. Queries as Virtual Tables In Microsoft Access, you can use a row-returning query (sometimes called a view) almost anywhere you can use a table. This allows for great flexibility in your forms, reports and programsthese objects simply expect a row-and-column result set of data, and dont care whether it is a physical result set (table) or a virtual one (query). The Flipper application, below, relies heavily on this feature of Microsoft Access. Updatable Views With only a few exceptionsprimarily crosstab queries and queries that return aggregate datarow-returning queries in Microsoft Access are fully updatable. In most cases, you can join as many tables as you want in a single query and allow your user to update columns across the entire query, from any or all tables. This even works with queries that include outer joins, for example, joins where there is no match in one or more tables. This is especially powerful for applications where you need to present a unified view of data across many tables, and allow the user to edit it without jumping through hoops. Dynamic Lookup Whenever a foreign key value is changed in a Microsoft Access query, Microsoft Access automatically looks up new data from the primary table. Data-Bound Combos and List Boxes Microsoft Access combo boxes and list boxes can be populated from tables or queries, can display multiple columns, can selectively hide returned columns, and can bind any one of the displayed columns to an underlying field in the form. Combos also support the NotInList event, which allows you to trap a user entry that is not in the combos drop-down list and take definitive actionprompt the user, add it to the list automatically, whatever you like. The Smart List example below makes good use of the NotInList event. Subforms Microsoft Access subforms let you quickly and easily set up applications where navigating one set of data, for example, customers, automatically causes navigation in a related data set, for example, orders. Run-time-Settable Properties Virtually all Microsoft Access form and report properties are settable at run time. The ability to change property settings dynamically, including data binding, combined with Microsoft Accesss overall power, makes for some awesome applications. The Flipper application below is a particularly good example. Navigator The Navigator is a sophisticated custom control that works with Microsoft Access. It lets you quickly create applications that navigate through related data. The Navigator is powerful even with very deep hierarchies, for example, customers-orders-line items-products-suppliers. It is beyond the scope of this talk, but well worth learning about if your applications have these kinds of data navigational requirements (most applications of even moderate size do). Wizards Wizards are programs written in Microsoft Access Basic that allow you to automate complex and repetitive tasks, like building tables, queries, forms, reports, or controls. The form, report, table, and query wizards included with Microsoft Access itself are all good examples, and all were written in Microsoft Access itself. The ultimate goal for many Microsoft Access developers is to discover the regular structure in their applications and then create wizards that pump out user-defined versions of that regular structure at will. Wizards, too, are beyond the scope of this talk, but well worth learning about if your applications can benefit from complex, repeatable structures. If you want examples to start with, both Smart List and Flipper, below, are good candidates for being wizard-ized. Example 1: Smart List (1M) Goal Smart List lets you enter data into a one-to-many database structure as easily as if you were entering it into a spreadsheet, while the database works behind the scenes to ensure data integrity. The Problem Suppose you want to keep track of recording artists and their songs. You will typically have multiple songs per artist, you would like your data arranged in a list, and you would like to be able to sort either by artist or by song title. (For simplicity, I am ignoring the possibility of multiple artists per song.) You would like your list to look something like this: Artist Song  Beach Boys Help Me Rhonda  Beach Boys Good Vibrations  Beatles Lady Madonna  Beatles Hey Jude  Beatles Let It Be  Beatles All My Lovin  Beatles Good Day Sunshine  Beatles Eleanor Rigby  Bob Dylan John Wesley Harding  Bob Dylan Like a Rolling Stone  Spreadsheet Solution Before we solve this problem with Microsoft Access, note that you can solve it very easily with a spreadsheet. Although this solution wont offer the user much power, it does have the advantage of being quick and simple. Advantages The advantages of the spreadsheet solution are: Quick and easy. Give the user a spreadsheet program, and they can create the list for themselves. Intuitive. Works pretty much like a list you would keep on pencil and paper. Disadvantages The disadvantages of the spreadsheet solution are: No uniqueness of data. If you have entered a hundred Beatles songs, you have entered a hundred different artists (as far as the spreadsheet is concerned). No data integrity. If, after entering lots of his songs, you decide to change Jimmy to Jimi, you will have to search out and change the text everywhere Mr. Hendrix occurs in your list. No system intelligence. It doesnt matter that you have already entered an artists name numerous times. When you enter a new song by that artist, you will have to enter her name again. If you make a typo when entering an artists name, you will get no help from the system, even though you have previously entered the name correctly. The first two points seem cumbersome enough in this simple example. They become a whole lot worse when you want to keep track of more data items for each artist (for example, date of birth, hometown, picture, middle initial, and so on). Note that similar observations apply to solving this problem with a flat-file database program, sometimes called a filer. Microsoft Access Solution You can solve this problem very elegantly using Microsoft Access (otherwise there wouldnt be much point to including it in this paper!). It will take some work on your part, but you will create a solution for your user that has the simplicity of a spreadsheet and the power of a database. The basic data structure of Smart List in Microsoft Access is a pair of tables like these: Artists  Songs   primary key ArtistCode symbol 232 \f "Wingdings" \s 8 ArtistCode foreign key   ArtistName  SongCode    ...  SongName      ...   (Of course, your database might have Customers and Orders, instead of Artists and Songs, but you get the idea.) The tables are joined by a primary key/foreign key pair. Its customary for these key fields to have the same namethough not necessary, your system will be more self-documenting if you use a common name. Note that you could have additional dependent fields in either tablefor example, an artists birthday or a songs copyright dateand the problem would still have the same essential structure. This possibility is represented by the ... fields in the diagram. SmartList is based on a form bound to the Songs table. The form contains the following controls: A text box on the form, bound to SongName. An unbound option group control in the forms header that allows the user to switch between update and insert behavior for entries not found in the drop-down combo list of Artists. (See Issue below for why this option group is necessary.) A combo box on the form bound to the field ArtistCode in the Songs table, for example, the foreign key ArtistCode. Note: This combo is where all the action happens! The Row Source for the combos drop-down list is the Artists table. The combo displays values from ArtistName, but stores (invisible) values from ArtistCode. The AutoExpand property of the combo is set to Yes. (The setup described here is the most common use of bound combos in Microsoft Access: displaying name-like information along with an invisible primary key, and storing the primary key values into a foreign key field.) How a Combo Displays, Reads, and Stores Data Since the combo is the scene of the action, it is worth examining in more detail. When the user picks an item from this combo, Microsoft Access does the following behind the scenes: (1) When the user selects an ArtistName from the list ...     symbol 175 \f "Symbol" \s 12   (2) ... the combo reads the invisible ArtistCode ... symbol 175 \f "Symbol" \s 12    symbol 175 \f "Symbol" \s 12 symbol 175 \f "Symbol" \s 12    JimiHendri Jimi Hendrix symbol 234 \f "Wingdings" \s 8    LedZeppeli Led Zeppelin     RollingSto Rolling Stones     . . . . . .         (3) ... and stores it in the foreign key field in the Songs table.   symbol 175 \f "Symbol" \s 12    ArtistCode SongCode SongName    JimiHendri 17 Purple Haze    . . . . . . . . .    . . . . . . . . .    . . . . . . . . .   There is also Microsoft Access Basic code behind the combo box control, attached to the NotInList event. This code fires whenever the user enters a name not found in the combos drop-down list. Based on the current setting of the option group, this code either updates the current name in the list, or inserts a new name and code, and stores the new code in the combo. Inserting via the Combo If the user has chosen to insert a new entry, the following happens behind the scenes: (1) When the user enters an ArtistName that is not in the list ...     symbol 175 \f "Symbol" \s 12   (2) ... the code attached to the NotInList event fires, which in turn... symbol 175 \f "Symbol" \s 12     symbol 175 \f "Symbol" \s 12     Barry Manilow symbol 234 \f "Wingdings" \s 8    JimiHendri Jimi Hendrix     RollingSto Rolling Stones     . . . . . .         (3) ... creates a new record in the Artists table ...   symbol 175 \f "Symbol" \s 12 symbol 175 \f "Symbol" \s 12   ArtistCode ArtistName    BarryManil Barry Manilow    . . . . . .    . . . . . .    . . . . . .         (4) ... and then re-queries the combo and selects the newly entered item.    symbol 175 \f "Symbol" \s 12 symbol 175 \f "Symbol" \s 12    BarryManil Barry Manilow symbol 234 \f "Wingdings" \s 8    JimiHendri Jimi Hendrix     RollingSto Rolling Stones     . . . . . .    Updating via the Combo If the user has chosen instead to update the existing entry, then the following happens: (1) When the user enters an ArtistName that is not in the list,  for example, to correct the spelling of an important name ...     i   (2) ... the code attached to the NotInList event fires, which in turn... symbol 175 \f "Symbol" \s 12     symbol 175 \f "Symbol" \s 12     Jimmy Hendrix symbol 234 \f "Wingdings" \s 8    LedZeppeli Led Zeppelin     RollingSto Rolling Stones     . . . . . .         (3) ... edits the ArtistName field in the Artists table (and, optionally, edits the ArtistCode) ...   symbol 175 \f "Symbol" \s 12 symbol 175 \f "Symbol" \s 12   ArtistCode ArtistName    JimiHendri Jimi Hendrix    . . . . . .    . . . . . .    . . . . . .         (4) ... and then re-queries the combo and selects the newly entered item.    symbol 175 \f "Symbol" \s 12 symbol 175 \f "Symbol" \s 12    JimiHendri Jimi Hendrix symbol 234 \f "Wingdings" \s 8    LedZeppeli Led Zeppelin     RollingSto Rolling Stones     . . . . . .   (Jimi, sorry for misspelling your nameit was only to make a point.) An issue: What did the user mean? An interesting issue arises with Smart List that doesnt occur when you make a list in a spreadsheet: When the user types over a name in the combo with an entry that is not already in the list, did he or she mean to update the existing name or enter a new name into the list? In a spreadsheet, it is irrelevant what the user intended: whatever he or she types is entered into the current cell, and that is that. With Smart List, Microsoft Access will respond intelligently by either updating the name everywhere it appears (because, of course, the name is only stored in a single place in the Artists table), or by entering a new name into the drop-down list automatically and then storing its ArtistCode in the current record in the Songs table. However, there is no way for Microsoft Access to know whether the user intended to overwrite the current name or add a new one; hence, the option group, which acts as the users switch to indicate his or her intentions. (You could certainly implement a slicker user interface than this simple option group switch; the option group simply controls the behind the scenes behavior in a way that is visible on the form, to make this example more understandable.) Advantages The advantages of writing Smart List in Microsoft Access are: Data integrity and uniqueness are guaranteed. Even if you enter a hundred Beatles songs, you have only entered Beatles once, in the Artists table. If, after entering lots of his songs, you decide to change Jimmy to Jimi, you only need to change it once in your list, in any occurrence. All other occurrences are changed automatically. System intelligence. When you enter a new song by an artist that is already in the list, Smart List will automatically select the artists name for you, and will even complete the name automatically after you have typed the first few characters. Typos and other data entry errors are minimized. Disadvantage About the only disadvantage of Smart List is that someone has to create a form and write some code to make it work. Therefore, most end users would not be able to create Smart List by themselves. This disadvantage can be overcome by creating a Microsoft Access wizard that in turn creates Smart List applications based on a few simple user inputs. Example 2: Flipper (MM) Goal Flipper gives you flexible capabilities for entering, updating, and navigating through MM-related objects, like students and courses. The Problem Suppose you want a way to keep track of pairs of objects, and how they relate to each other. You might want to see students and their courses ... Students    Courses   Student ID First Name Last Name  Course ID Course Name  95 Robert King symbol 232 \f "Wingdings" \s 8 ENG202 American Novel     symbol 232 \f "Wingdings" \s 8 PH101 Intro to Physics     symbol 232 \f "Wingdings" \s 8 GEO99 Rocks for Jocks     symbol 232 \f "Wingdings" \s 8 MA1 Math Blaster  ... or, you might want to see courses and their students ... Courses    Students    Course ID Course Name  Student ID First Name Last Name  GEO99 Rocks for Jocks symbol 232 \f "Wingdings" \s 8 87 Nancy Davolio    symbol 232 \f "Wingdings" \s 8 95 Robert King    symbol 232 \f "Wingdings" \s 8 134 Laura Callahan   You might want to see a pair of lists, as above, or you might want to see all the gory details on a particular item, along with its list of related items ... Course Info  Students    Course ID PH101  Student ID First Name Last Name  Course Name Intro to Physics  87 Nancy Davolio  Hours 7:00-8:30 M-W-F  95 Robert King  Professor Albert Einstein  134 Laura Callahan  Teaching Asst Bozo the Clown      Pre-requisite Advanced Cosmology       Photo  [picture of Al and Bozo at the beach.]      Last but not least, you might want to see lots of other things presented in the same way ... Customer Info  Product Sales      Customer ID DMARK  Product ID Product Name Jan Sales Feb Sales ...  Customer Name Dons Mark-Up  87 Coke 100 78 ...  Address 88 Rip-off Street  95 Pepsi 65 130 ...  City Downtown  134 Royal Crown 99 99 ...  State OK        Zip 99555         Photo  [picture of Don raising prices.]        Flipper Features Flipper gives you flexible capabilities for handling MM-related objects, both directly-related ones like students and courses, and indirectly-related ones like employees and their sales to various customers. (Remember our earlier discussion of obvious and subtle MM relationships.) Flipper lets you: View the objects side-by-side, automatically displaying only related objects as you navigate. Flip your viewpoint back and forth between object types. For example, toggle between views of courses-by-student and students-by-course. View your objects as a pair of lists, or as detail with a list of related objects. Enter assignments between objects, assuming this makes sense. For example, enroll a student in a course. Besides these features, if you buy before midnight March 31 (and even if you dont), Flipper comes complete with Drill-Across, which is sort of like horizontal drill-down. If you are looking at student Nancy Davolio and her course list, you can double-click on the Rocks for Jocks course, and, presto, you are looking at the student list of Rocks for Jocks (including Nancy, of course). Spreadsheet Solution Because this is another list problem (at least in some sense), you might ask how we could solve it with a spreadsheet or filer, as we did with Smart List. However, the problems you run into, which are bad enough in a 1M situation, become a lot worse with MM-related data. A spreadsheet or filer wont begin to handle issues like data navigation, flipping views, changing from detail to list display, not to mention the soon-to-be-famous Drill-Across. Microsoft Access Solution Again, you can solve this problem very elegantly using Microsoft Access (no surprise!). The solution takes very little code, even less code than the modest amount we created for Smart List. The underlying data structure of Flipper is: a pair of tables or queries that show students and courses (or two related objects of your choosing), and a junction table or query that relates students and courses. Students  Junction    StudentID symbol 232 \f "Wingdings" \s 8 StudentID  Courses  StudentName  CourseID symbol 231 \f "Wingdings" \s 8 CourseID  ...  ...  CourseName      ...  Again, note that you could have additional dependent fields in any of the tables or queriesfor example, a students birthday, course hours, gradesand the problem would still have the same basic structure. Technical AlertIf the next few paragraphs begin to sound like jargon, I recommend looking in Microsoft Access Help or the Microsoft Access documentation for definitions of terms like subform, LinkFields, and so on. Flipper uses an unbound form with two unbound subforms on it. The subforms get bound by setting their SourceObject property at run time based on the users input. The left-hand subform takes the role of the parent form, and the right-hand subform the role of the child form. The form also contains an unbound text box, which links the data in the right-hand (parent) subform with the current record in the left-hand (child) subform. It all kinda works like this: (1) When the user selects a course in the left-hand subform ...  symbol 175 \f "Symbol" \s 12   symbol 175 \f "Symbol" \s 12 (2) ... the LinkValue field is updated with the current course ID ...  symbol 175 \f "Symbol" \s 12 symbol 175 \f "Symbol" \s 12      symbol 175 \f "Symbol" \s 12  PH101   symbol 175 \f "Symbol" \s 12   (3) ... and the right-hand subform then shows related students,  symbol 175 \f "Symbol" \s 12  by joining from the LinkValue field to the hidden Course ID field  symbol 175 \f "Symbol" \s 12      symbol 175 \f "Symbol" \s 12  Course ID PH101  Student ID First Name Last Name Course ID  Course Name Intro to Physics  87 Nancy Davolio PH101  Hours 7:00-8:30 M-W-F  95 Robert King PH101  Professor Albert Einstein  134 Laura Callahan PH101  Teach. Asst Bozo the Clown      Pre-requisite Advanced Cosmology       Photo  [picture of Al, Bozo and Jimi at the beach, sifting the sands of time.]      Under the hood, the left-hand subform is bound to the Courses table, while the right-hand subform is bound to the junction table (for example, Students-Courses). The right-hand subform uses the magic of a Microsoft Access combo box to display useful student information, like Name, even though the only data it stores is Student ID. Now this is where all the action takes place: The SourceObject property of each subform control. The ControlSource property of the unbound LinkValue text box. The LinkChildFields property of the right-hand subform control. Flipper also contains a generic mechanism for letting the user choose which views of data he or she wants to see, and for flipping back and forth between inverse views (like students-by-course and courses-by-student). The view mechanism is handled with an unbound list box, which could either be on the Flipper form itself, say in the header or footer, or on a separate pop-up form. The list box contains specs for the various views which might be interesting to users. The specs are stored in a Microsoft Access table, so they are easy to create and maintain. The information stored for each spec looks like this: Even More Technical Alert - If the above sounded like jargon, wait til you see what follows! However, this will all make sense in a hurry when you see the example in action. SpecID SpecName LeftSource LeftLink RightLink RightSource FlipID  1 Students >> courses Students Student ID Student ID rCourses 2  2 Courses >> students Courses Course ID Course ID rStudents 1   SpecID is the primary key (counter) for the Specs table, where all this information is stored. SpecName is the friendly name you show to the user in the Specs list box. LeftSource is the name of the form that gets stuffed into the SourceObject property of the left-hand subform control. RightSource is the name of the form that gets stuffed into the SourceObject property of the right-hand subform control. LeftLink is the name of the control on the left-hand subform that gets stuffed into the ControlSource property of the unbound LinkValue text box. RightLink is the name of the field on the right-hand subforms underlying table or query that gets stuffed into the LinkChildFields property of the right-hand subform control. FlipID is the SpecID of the spec to flip to when the user says Flip!. This all sounds pretty complicated (what an understatement!), but believe me, it works much more easily than it sounds. Run-time-Settable Properties Flipper is a great showcase for run-time-settable properties. As you can see in the bulleted list above, the entire application hinges on the ability to change complex, powerful properties like SourceObject at run time. Queries as Virtual Tables Flipper is also a great showcase for queries as virtual tables. This is what allows the application to work equally well with either table-based MMs, such as students-courses, or with query-based MMs, such as customers-orders-details-products-suppliers. So ... You Wanna Write a Wizard? Wizards are programs written in Microsoft Access Basic that allow you to automate complex and repetitive tasks, like building tables, queries, forms, reports, controls, and even entire applications. To write a wizard that automates the creation of an application, you first need to isolate the regular structure of the application you want the wizard to create. In most cases, this structure will consist of the table(s), fields, queries, form(s), form fields, property settings, and event code that collectively captures the behavior of the application. You can also choose to simplify a wizard by requiring that the tables or queries already exist (the form and report wizards included with Microsoft Access work this way). Both Smart List and Flipper are good candidates for being wizard-ized. The above material on the two applications and the code in the Appendix should give you a good head start if you want to create wizards for either or both. Conclusion The Importance of Run-time-Settable Properties, Queries as Virtual Tables, Updatable Views, Data-Bound Combos and List Boxes . . . It is kind of amazing to look at applications like these, and realize that there is so little that you as the application developer need to do to make it all happen. Microsoft Access was designed to make data navigation and manipulation accessible to more and more peopleit does this is by lowering the barriers to being an application developer. In Flipper, for example, all that needed writing was a tiny amount of code behind the form and a table of specs that define views of interest to the user. The work involved in creating applications like these is mainly envisioning what the user wants to see, and figuring out the Microsoft Access mechanisms that deliver it most efficiently. Now that that work is done and explainedthis paper, the Tech Ed talk, sample codeyou can take advantage of it without having to invent it from scratch. Terms like run-time-settable properties, queries as virtual tables, updatable views, data-bound combos, and list boxes, ... can sometimes sound like just so many buzzwords. This paper and the Tech Ed talk should give you some sense of how they can be applied productively to real-world data management problems. Appendix: Code for Examples Code for Smart List Sub ArtistCode_NotInList (NewData As String, Response As Integer)    Dim DB As Database, MySet As Recordset  Dim Criteria As String    Set DB = CurrentDB()  Set MySet = DB.OpenRecordset("Artists", DB_OPEN_DYNASET)    DoCmd DoMenuItem A_FORMBAR, A_EDIT, A_UNDOFIELD, , A_MENU_VER20     Select Case WhenNotListed   Case WhenNotListed_UPDATE   Criteria = "ArtistCode = " & "'" & ArtistCode & "'"   MySet.FindFirst Criteria   MySet.Edit   Case WhenNotListed_INSERT   MySet.AddNew   End Select     KeyValue = FriendlyKey(NewData, 10, True)   MySet!ArtistCode = KeyValue   MySet!ArtistName = NewData   MySet.Update   ArtistCode.Requery     Select Case WhenNotListed   Case WhenNotListed_UPDATE   Case WhenNotListed_INSERT   ArtistCode = KeyValue   End Select     MySet.Close   DB.Close     Response = 0   DoCmd GoToControl "SongName"    End Sub   page 2 symbol 183 \f "Symbol" Title Microsoft Techsymbol 183 \f "Symbol"Ed '94 Microsoft Techsymbol 183 \f "Symbol"Ed '94 Title symbol 183 \f "Symbol" page 1 page 20 symbol 183 \f "Symbol" ref coursenumberAC211 ref titleDatabase Building Blocks Microsoft Techsymbol 183 \f "Symbol"Ed '94 Microsoft Techsymbol 183 \f "Symbol"Ed '94 ref coursenumberAC211 ref titleDatabase Building Blocks symbol 183 \f "Symbol" page 21 Microsoft Techsymbol 183 \f "Symbol"Ed '94 ref coursenumberAC211 ref titleDatabase Building Blocks symbol 183 \f "Symbol" page 23 stv} @ h v,stx8hst 8stx8h-.FHWXpqst\]uw./456@k v {     , - K M k m o p   $ & L& 1 2 P R s      ' ( F H c e g h  (2467UW_km46SUuw) N)+gh!2Xd>?]_9;]_mo"#ACgimn "EGK PKLjl{, .!0!!R!S!q!s!!!!!!!!!!!!!""."0"6"7"U"W"r"t"z"{"""""""""""####8#:#T#V#\#]#{#}#######R###$$$%%%%-%.%L%N%d%f%j%k%%%%%%%%%%%%%&&%&'&=&?&C&D&b&d&x&z&~&&&&&&&&&&&'''''9';'S'U'a'c'v'w''''''*)8)<)N)))))****R*(***6*8*C*E*P*R*[*]*j*l*x*z***+,{666666889}99999 : :(:*:L:N:R:S:q:s::::::::: ;;;;1;3;U;W;[;\;z;|;;;<<<\<^<j<l<s<t<<<<<<<<< ="=.=0=U0=7=8=V=X=========== > >B>D>???B]CzC|CCCCCD D,D.D2D3DQDSDvDxDDDDDDDDDDDEE5E7E;Ej@jNj[j]jmjyj{j}jjjjjjjjFmnnnnnoppqqqqqrr rBrCr_rarcrerirjrrrrrBrrrrrrrrrrrsss"s2s:sAsHsLsXsssssssssss tt%t+t@tFt[t_tuuv vCvvvvvvvvvvwww!w'w(wDwFwJwNwPw_w¾@_w`w~wwwwwwwwwwwwwww#x%x'x(xDxFxHxIxexgxkxmxxxxxxxxxxxx=y?yCyDy`ybydyeyyyyyyyyyyyyyyy zz?zz#z'z)zAzczizzzz{%{({,{v{w{{{{{{{{{{{{{{{{{{| ||"|.|>|F|M|T|X|d||||||||| } }}}+}1}K}Q}_}    =_}e}s}y}}}}}}}}~~ ~ ~&~(~,~0~<~J~K~i~k~s~~~~~~~~~~48ʆ(AGΈۈnȉʉ܉݉ >@[]cdŊNJ܊ފJފ8:wyȋʋ΋ϋ (*BF=?L^`tv}؍ڍ&.09ckmˎ B        ABDSbd|~Ǐɏ&JVXjJǓȓєȖ\vx˘ſ               8˘͘טؘ  "*/1ޚ468ŝ˝͝Ν  !       6`bcȞʞ˞#*OZ\i{}Ɵ͟ϟڟ )13BV^`i0          90>;azY[#Ӭŵڵܵ23IKSUcdz|     !Bպ׺ٺں&'=?WXnpwxλлһӻػٻۻܻ+,1245>?WXZ[qsuv{I{|~Fs6@        , O U k m o ɟoiiii)+.4           )+.4 +. N    " $ & 1 T ] ЦvppppF@@@@)+.4           )+.4     )+.4         )+.4                 ' J P c e g ЦvppppF@@@@)+.4           )+.4     )+.4         )+.4          "(246Y_kЦvpppj@:::4)+84       )+.4             )+.4     )+.4         km)46<CEſ~xxxxxGAAA0+.@[) sA +}W% o=+.[) sAx l'+4         EKSU[cemuwÒ[UUUUU0+.@[) sA                 0+.@[) sA                 0+.@[) sA                  )+!2XdɘaZZNID?: ld0+.@[) sA         0+.@[) sA                 0+.@[) sA               d v &>ahoz…~voggg`/<+Xa2 Q }+a2 Q } üpiiiaaaZ<+Xa2 Q }                <+Xa2 Q }                    =DKV]_acegikmüpiiiaaaZ<+Xa2 Q }            <+Xa2 Q }                mov üpiiiaaaZ<+Xa2 Q }                        <+Xa2 Q }         "ELS^gikmüpiiiaaaZ<+Xa2 Q }                <+Xa2 Q }                $+2=EüpiiiaaaZ<+Xa2 Q }                <+Xa2 Q }                EGIKnu|üpiiiaaaZ<+Xa2 Q }                <+Xa2 Q }                , !!!!!.!0!2!4!6!8!:!!B!I!R!u!|!!!!!!!!!!ýzsmmmf`*5+LZ3+  3u         5+LZ3+  3u                   5+LZ3+  3u   !!!!!!!!!!!""."0"2"4"6"Y"`"r"氩`YSSSLF5+LZ3+  3u                     5+LZ3+  3u     r"t"v"x"z""""""""""""ýzsmmmf`*5+LZ3+  3u       5+LZ3+  3u         5+LZ3+  3u         """"""#####<#D#T#V#X#Z#\####氩`YSSSLF5+LZ3+  3u                     5+LZ3+  3u     #########$$$$$$$$$$$$$%Ľ|vvvvvve______+Z ? 5+LZ3+  3u         5+LZ3+  3u         %%% % %%%%%%-%P%T%[%d%f%h%ýzzsmm705+LZ ?                   5+LZ ?     5+LZ ?  h%j%%%%%%%%%%%%%%%%%%%g`ZZSMM5+LZ ?              5+LZ ?              %%%&)&-&4&=&?&A&C&f&j&r&x&z&ýzsmmf``*5+LZ ?              5+LZ ?                      5+LZ ?        z&|&~&&&&&&&&&&&&&&&''''氩`YSSLFF5+LZ ?        5+LZ ?              ''''='B'I'S'U'W'Y'[']'_'a'c'ýzsmmf``*5+LZ ?        5+LZ ?              5+LZ ?                      c'h'v''''''')))))******!*(*氫nggMFF+ `        + ` +b5+LZ ?                      (***.*6*8*<*C*E*I*P*R*V*[*]*b*j*l*q*x*ž||b[[A::+ `        + `       + `       + `       + `        + `       x*z*****+,-i2r45{668}9999999999999999+a _G*h+ `       + `       999 :,:3:::E:L:N:P:R:u:|::::ýsmmmmf``<+Xa _G*h                    <+Xa _G*h:::::::::::::::; ;ýsmmmmf``<+Xa _G*h        <+Xa _G*h                 ;;;;5;<;C;N;U;W;Y;[;~;;;;;ýsmmmmf``<+Xa _G*h                <+Xa _G*h                        ;;< < <<<<<<*<,<:<H<T<\<^<`<b<d<f<h<j<þkeeeeee5+L}u ]@~ +}u ]@~<+Xa _G*h                j<l<s<<<<<<<<<= == ="=$=&=(=Ľ{{{tnn82225+L}u ]@~                5+L}u ]@~                   5+L}u ]@~    (=*=,=.=0=7=Z=t={==========󽷷tnnngaa5+L}u ]@~                      5+L}u ]@~       ===>(>/>:>B>D>??H@B]ChCjClCtCvCxCzC|CCCCCCCCĽ|wrrmgggggggUOOOOOOO+a _G*h5+L}u ]@~                5+L}u ]@~               CCCC DDD%D,D.D0D2DUD\DcDnDvDýsmmmmf``<+Xa _G*h                    <+Xa _G*hvDxDzD|D~DDDDDDDDDDDDDýsmmmmf``<+Xa _G*h        <+Xa _G*h                DDDDEE#E.E5E7E9E;E^EeElEwEEýsmmmmf``<+Xa _G*h                <+Xa _G*h                        EEEEEEEEEEEEEEEEFF FF)FLFþICC<<+Xa G/P+a G/P<+Xa _G*h                LFSFZFeFlFnFpFrFFFFFFFFFFFFFSMMF@93<+Xa G/P                <+Xa G/P                    FFFFF GGG%G,G.G0G2GUG\GcGnGuG`ZZSMF@@<+Xa G/P                        <+Xa G/P        uGwGyG{G~GGGGGGG KKKLL NN(O?OJPýfa\\WRMHC>94<+Xa G/P                <+Xa G/P                JPeP RRTT!UCUNWXW(XFX{YYV[_[\w]^^^f_s_````aaa aĿuooSM(      (  a1a3a l~ l~%l6@      %l6@      ({*{,{v{{{{{{{{{{{{{{{| |||ûe^^^XQ0l@@     l  dldl@ d || |"|.|>|@|B|D|F|M|T|V|X|Z|\|^|`|b|d|||Ơf_____MG9 l2l@  %l6@      %l6@      %l6@      |||}}}}+}-}/}1}=}K}M}O}Q}X}_}a}c}e}l}s}u}⽶||uUNHHAl*@  l*@  $l*@   l @ u}w}y}}}}}}}}}}}}}}} ~*~,~.~0~<~J~m~̬xxxxg``ZTMl @  ll@  l*@  l*@  m~o~q~s~~~~~~~~~~~~~~~~¼|uOHHB<5%l6@      %l6@      0l@@     ~~4IҀ8ʆ(AGΈۈnxz|~ȉ{{{{{{+ } %l6@      ȉʉΉ։܉BI[]_acýKEEEEEE5+L }          5+L }                      5+L }  ɊΊ܊ފ%')468:ERT`lwĎrllllll+ 5+L }          5+L }        wyȋʋ̋΋ ,Ľ{{{tnn82225+La               5+La                       5+La   ,18BDF&2=?L^OIC5+Le   +e 5+La               ^`dktv}Ǎ΍؍񻵯jd^UOOO5+Le   5+Le   ؍ڍ&(*,.0ľpjd[SLL*!+$e  5+Le   5+Le   029;cegikmˎ͎܎ގ '2=Bܺ~xxxxx2+$ C E[{2!+$e  BDSbdhnsw|~HA;2+2C+d$ C E[{ 2C+d$ C E[{ ǏɏΏۏߏy5.2C+d$ C E[{ 2C+d$ C E[{ &(JL➗yyyySLLFF=2%+($ C E[{ 2C+d$ C E[{ LNPRTVXjޒJєȖ\fhrtvx{unn+F 0O%+($ C E[{ ˘͘ט  "$&(*}voi:3333.+@F 0O  .+@F 0O   $+,F 0O */1ޚ8Ýŝǝɝ˝͝~ogg````ME>>>+.$ g5+.+. +...+@F 0O `bȞʞ#*,8DOZɹ{ga[TTTTT+ e3+ $ + $ +.*4    Z\i{}Ɵ͟c]WPJJJD>+.X e3 JJ>+.X e3 JJJJJ͟ϟڟ )+-/1c]WPPPP>+.X e3 JJJJJ>+.X e3 JJ13BVXZ\^`bik ;qa¼~~~~ZUUPPPKF#+P$ e #+.$ e 7+.L e  #/9DQY[^s}ŦЦۦg_XXXXXP<(X J                             <(X J IIIIIIIIIIIIIIIIIIIIIIIIIIIIM#֩#Ӭ{nŵڵܵ!#%þ|voc\P l, l,<(X J                             %MOgikmöŶ/1TV{thaUNB; l, l, l, l, l, l, l, l, l, l,ٷ۷,.46egǻ{oh\UIB l, l, l, l, l, l, l, l, l, l,ظڸݸ߸#%HJmoǻ{oh\UIB l, l, l, l, l, l, l, l, l, l,SUGI޻ǻzzzzuuppk      h l, l, l, l, l, l," Body TextCODEcaption Bullet List 2 Bullet List Table TitlebylineBioBullet List BodyGraphic Table Text Table Head Number Listt1t2t3t4t5trbll2l3zzp Heading 2xFooterb$<F       !    * & ! < ,&*j+& * 0  sD/F N V^xx Fx xp p  x ((                $*   5(v Ǽּ]Q_s  Tms Rmn `Symbol Helv 0CourierTimes New Roman Arial MS Serif MS Sans SerifTimes Helvetica System0Courier New AvantGardeITC BookmanHelvetica-NarrowNewCenturySchlbk PalatinoZapfChanceryPZapfDingbats 0ModernRoman @Script WingdingsMicrosoft Logo Arial NarrowBook AntiquaBookman Old Style Century GothicCentury Schoolbook@Monotype CorsivaMonotype Sorts0MS LineDraw PAlgerian Arial Rounded MT BoldPBraggadocio Britannic Bold@Brush Script MT PColonna MT PDesdemonaFootlight MT Light Impact PKino MT Wide Latin@Matura MT Script Capitals PPlaybill MSIcons MS Dialog0LinePrinter0Lucida Sans Typewriter0Letter Gothic MSSmall Fontshn}$15ntu56   < [ \  $ % L k l   H g h  &'Qpq/0Yxy  7VW56 5 6 s !!=!\!]!!!!!!! "?"@"#### $ $%$D$E$$$$$$$$%%_%~%%%%%%&&888888I9h9i9999999:;;K;j;k;;;;<0<1<k<<<iBBBBBB)CHCICrCCCCCCDDDDEEiEEEEEEhhhgpppppppqq q'q(qKqjqkq r=r>ru,u-u~uuuuuuuuuvvvvvvwwwwxx&xExFxyzzz